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