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