[geeklog-devel] MySQL performance - Use of Indexes

Blaine Lang geeklog at langfamily.ca
Tue Feb 8 15:52:33 EST 2005

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:
    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'
    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.

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

More information about the geeklog-devel mailing list