[geeklog-devel] MySQL performance - Use of Indexes

[Dwight Trumbower]

OR are usually always timely in any DB. Just like using IN or NOT IN. In
other DBs you can usually run a query plan to find this information out. I
haven't heard if MySQL has that capability yet.

You just were reminded that MYSQL was never designed to be a "true" RDMS.
It was great for read/reporting type solutions.

Postgres latest release is suppose to be really nice. I want to look at it
for future projects, but it won't be a while before I can look at it.
Unless someone pays me. :)




> 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
>
>
> _______________________________________________
> geeklog-devel mailing list
> geeklog-devel at lists.geeklog.net
> http://lists.geeklog.net/listinfo/geeklog-devel
>


-- 
Dwight Trumbower