I don't post in this SE often, so if I'm not explaining the query properly please let me know and I will edit.
I have to obfuscate some of the work that I am doing so I will try to explain my issue in generic terms and hope that I can get some answers on what to investigate.
I have a large table with a variety of columns. But for the purposes of this question, let's just say it is
mytable and has columns
If I do:
SELECT * FROM mytable
This query will take a very, very long time to run as in multiple hours.
But if I do something like
SELECT * FROM mytable WHERE Category=1
This query will run very quickly. In fact, assuming I only have 2 categories, I can do something like
SELECT * FROM mytable WHERE Category=1 UNION ALL SELECT * FROM mytable WHERE Category=2
And this query will run in mere minutes and still contain all of the records that the first query should have pulled back (again, assuming only 2 categories).
It should be noted that the following query does not show improvement over the benchmark, its is only the
UNION statement that fixes it.
SELECT * FROM mytable WHERE Category IN(1,2)
It's counter intuitive to me that one basic query can be slower than a
UNION of two queries. Why is that? To me, this goes beyond the usual problems with indexing - if the indexing wasn't right, none of the queries presented would run well. So, I'm really at a loss as to what I should research.
Is this a symptom that sounds familiar to anyone and can point me in the right direction for further research?