List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 29 1999 5:36pm
Subject:Re: Speed up LIKE or other solution?
View as plain text  
>>>>> "Bob" == Bob Kline <bkline@stripped> writes:

Bob> On Tue, 28 Sep 1999, Steven Fletcher wrote:
>> 
>> select * from companies where add1 like "bolton%" or add2 like
>> "bolton%";
>> 
>> Again takes 2 1/2 minutes! If I do a seperate ".. where add1 ..." and
>> "... where add2 ..." they both take 1 second. The minute I put in an OR
>> it's like MySQL is skipping the indexes again.
>> 
>> I guess I could, of course, put the reults from the first query into a
>> temp table, but surely this shouldn't be the behaviour of MySQL?

Bob> Well, this is one of those areas of refinement of the query optimizer
Bob> that shows up in benchmarks used to differentiate one DBMS from another.
Bob> The longer a DBMS has been around, the more sophisticated it gets at
Bob> recognizing conditions like this.  I'm sure TcX will consider putting
Bob> this in the queue if it's not already there.  In the meantime, you do
Bob> know about the EXPLAIN syntax, don't you?

I agree.  Currently the MySQL optimizer is quite strong and can handle 
most normal cases + a lot of strange cases that other SQL server
misses.

The most 'normal' case that MySQL can't yet handle, is OR on different 
keys.  We did add a test for this in the newest benchmarks and you can 
see some comparisons of this at http://www.mysql.com/benchmark.html 

Bob> http://www.mysql.com/Manual_chapter/manual_Reference.html#EXPLAIN

Bob> Also, make sure the DBMS's distribution picture for your indexes is
Bob> up-to-date (see --analyze option for isamchk).

Bob> Finally, take a look at section 10.5, "How MySQL optimizes WHERE
Bob> clauses."

Note that you can fix the OR on different keys in your application by
using a temporary table and adding rows to it with INSERT ... SELECT.

Regards,
Monty
Thread
Speed up LIKE or other solution?(Steven Fletcher)28 Sep
  • Re: Speed up LIKE or other solution?Bob Kline28 Sep
    • Re: Speed up LIKE or other solution?(Steven Fletcher)28 Sep
      • Re: Speed up LIKE or other solution?Bob Kline28 Sep
        • Re: Speed up LIKE or other solution?(Steven Fletcher)28 Sep
        • Re: Speed up LIKE or other solution?Michael Widenius29 Sep
  • Re: Speed up LIKE or other solution?Dan Nelson28 Sep