[geeklog-devel] MySQL performance - Use of Indexes

[Blaine Lang]

This was new for me today and thought that I would share as it may also effect some GL SQL queries.

I have a client project with 3.2M records which is actually only 30 days of data :)
A query that I have was like this:
SELECT * FROM calllogs WHERE 
    StartTime < '2005-02-02 11:02:19' AND 
    (Source_Caller_ID='4162389001' OR Dest_Caller_ID='4162389001') 
    ORDER BY StartTime

All three fields are indexed fields. The query was taking 90 seconds to complete.
But if you ran each query for Source_Caller_ID and Dest_Caller_ID separate ( so instead of the OR ), each query would complete in under 1 second.

Changing the query to use 2 selects and a UNION resulted in a time of about 1 sec to complete.

SELECT * FROM calllogs WHERE Source_Caller_ID='4162389001'
    UNION
    SELECT * FROM calllogs WHERE Dest_Caller_ID='4162389001' 
    Order By startTime;

Reason: MySQL ONLY uses the first index and was then in effect doing tablescan's when the OR condition and second field was added.

Blaine
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist8.pair.net/pipermail/geeklog-devel/attachments/20050208/4855eda2/attachment.html>