[geeklog-devel] MySQL performance - Use of Indexes

[Tony Bibbs]

I'd argue against "that MYSQL was never designed to be a 'true' RDMS"

Their stance has always been to slowly add features so as to make 
performance and stability a priority.  MySQL 5.x will add most of what I 
feel I need as a developer.

My main gripe is mainly that they sure do take their sweet time getting 
those badly desired features done.  I've got the new Postgres 
installed.  IMHO, it's harder to administer than MySQL but most of my 
issues are covered in the documentation adequately.

--Tony

Dwight Trumbower wrote:

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