I do not post often in this SE, so if I do not explain the request correctly, thank you for letting me know and I will edit it.
I have to conceal some of the work I do to try to explain my problem in generic terms and hope that I can get some answers on what to investigate.
I have a big table with a variety of columns. But for the sake of this question, let's just say that it's
my table and has columns
If I do:
SELECT * Of the table
This request will take very, very long to run under several hours.
But if I do something like
SELECT * Of the table O Category = 1
This query will run very quickly. In fact, assuming I only have 2 categories, I can do something like
SELECT * Of the table O Category = 1 UNION ALL SELECT * Of the table O Category = 2
And this query will run in a few minutes and will always contain all the records that the first query should have extracted (again, assuming only 2 categories).
It should be noted that the following query does not show any improvement over the benchmark, it is only the
UNION statement that corrects it.
SELECT * Of the table O Category IN (1,2)
It's counterintuitive to me that a basic query can be slower than a regular one.
UNION two requests. Why is that? For me, this goes beyond the usual issues related to indexing: if the indexing was not correct, none of the queries presented would work properly. So, I really do not know what I should look for.
Is this a symptom that seems familiar to anyone and who can point me in the right direction for further research?