[geeklog-devel] MySQL performance - Use of Indexes

[Tony Bibbs]

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, 
sp's etc.

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.

--Tony

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