Optimizing a SELECT Query
When you have a slow running SELECT query, it may have 2 reasons
- You don’t have the proper index on your table to let the WHERE conditions filter efficiently.
- You have too many joins that join a lot of data before being able to discard unnecessary data
If your table has an index on fields "A,B" (one index here, not 2) and you have a WHERE condition on B = something, then that index cannot work.
Try creating an additional index on B.
WARNING: every new index you create will slow down INSERTs and possibly UPDATEs.
Joining to much data
If you have a query like:
SELECT * FROM T1 INNER JOIN T2 ON ... WHERE ...
SELECT *, all the fields of the tables are joined in memory, sometimes even before all the unnecessary rows are discarded by WHERE clauses.
This can use a lot of memory and can sometimes even page to disk: super slow!
If you don’t need all columns, start by select only the columns you need. It may result in much faster joins.
Another situation is
SELECT * FROM T1 INNER JOIN T2 ON ... INNER JOIN T3 ON ... INNER JOIN T4 ON ... WHERE ...
This may result in joining 4 tables on many lines before the WHERE clauses apply.
In this case, if we can"t have efficient indexes to reduce the number of lines that will be joined in memory, it may make sense to first SELECT IDs from a smaller query, maybe just from T2 INNER JOIN T3 and then select additional date from T1 and T4 in a second query. (This may also be handled with a subquery);