[geeklog-devel] MySQL performance - Use of Indexes
tony at tonybibbs.com
Tue Feb 8 16:05:25 EST 2005
That's ridiculous. I tell you, I see what Postgres has been doing (most
recently, the addition of table spaces) and I sometimes wonder why I use
MySQL. Sure it's easy to learn, fast, etc but man do I miss triggers,
This opens up the option of us wanting to review all our SQL in 1.3.x
and seeing if it may be effected by this. The search page comes to mind.
Blaine Lang wrote:
> 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'
> 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.
More information about the geeklog-devel