MySQL Forums
Forum List  »  Performance

Query not properly using indices
Posted by: Manuel Diez Silva
Date: August 31, 2023 06:10AM

Hello everyone,

I'm currently facing some challenges on column indexation and I would love to get some help. I have trimmed down the problem a lot to illustrate the issue better and it goes like this:

I have a query which has to retrieve all the rows from a table based on some foreign key ids from another table (foreign keys being filtered by one condition). This is the query:

SELECT *
FROM monitor
WHERE
zone_id IN (SELECT id FROM zone WHERE main = TRUE);

This query does not use the index for the column zone_id and it takes a very long time, whereas if I do this other query:

SELECT *
FROM monitor
WHERE
zone_id IN ('1','2','3','4','5','6','9');

It indexes the column properly and it's basically instant.

The array used is the result of the former subquery.
I have removed a lot more stuff from the query to make my point simpler, but tried this simpler scenario and got the same results.
Why is this happening and is there any way I can dynamically select the filter array?

Thanks a lot in advance!
Thanks in

Options: ReplyQuote


Subject
Views
Written By
Posted
Query not properly using indices
207
August 31, 2023 06:10AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.