List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 12 1999 9:47pm
Subject:Re: Trying to make queries faster
View as plain text  
>>>>> "Jan" == Jan Dvorak <jan.dvorak@stripped> writes:

Jan> Steven Fletcher wrote:
>> As an example query, looking for Museums in Salford, we have:
>> 
mysql> Select * From companies Where ((type like 'museum%') ) AND ( add1
>> LIKE 'salford%' OR add2 LIKE 'salford%' OR add3 LIKE 'salford%' OR add4
>> LIKE 'salford%' OR district LIKE 'salford%' OR town LIKE 'salford%' OR
>> county LIKE 'salford%' ) limit 50;

Jan> Add an index on type? Just guessing...
Jan> Anyway, try Explain and post the results here.

>> [...] MS SQL server 7, which as the same database is running on
>> such a machine, it's returning results in <1 second there.

Jan> MS SQL server can create indeces on its own.
Jan> It's part of their server-tunes-itself strategy,
Jan> which is not a bad thing.

Hi!

I don't think the above has anything to do with creating indexes of
its own.

The problem is more that currently the MySQL optimizer can't
optimize a query that uses OR between DIFFERENT keys (only
the same key).

When we implement UNION, we will fix the above at the same time.

The fix is to use:

CREATE TEMPORARY TABLE tmp SELECT * from companies where
(type like 'museum%') AND add1 LIKE 'salford%';
INSERT INTO tmp SELECT * from companies where (type like 'museum%') AND
add2 LIKE 'salford%';
INSERT INTO tmp SELECT * from companies where (type like 'museum%') AND
add3 LIKE 'salford%';
INSERT INTO tmp SELECT * from companies where (type like 'museum%') AND
add4 LIKE 'salford%'
INSERT INTO tmp SELECT * from companies where (type like 'museum%') AND
district LIKE 'salford%'
INSERT INTO tmp SELECT * from companies where (type like 'museum%') AND
town LIKE 'salford%'
INSERT INTO tmp SELECT * from companies where (type like 'museum%') AND
county LIKE 'salford%';
SELECT * from tmp LIMIT 50;

MySQL should be able to do the above in < 1 sec if you have keys on
add1,add2,add3,add4, district, town and county

Regards,
Monty
Thread
Trying to make queries faster(Steven Fletcher)9 Oct
  • Re: Trying to make queries fasterTonu Samuel10 Oct
  • Re: Trying to make queries fasterJan Dvorak12 Oct
    • Re: Trying to make queries fasterMichael Widenius12 Oct
  • Re: Trying to make queries fasterColin McKinnon13 Oct