CODEWITHSUNDEEP
phpmysqldatabase
Emil Dumbazu
Emil Dumbazu

Reputation: 662

Speed between two different queries

Which query is faster and why for a database with 1M records in a table ? How can I optimize the query ? First one:

SELECT id,column1,column2 FROM `table1` WHERE MATCH(column1) 
AGAINST('string1 string2 string3 ' in boolean mode) ORDER BY column2 ASC LIMIT 10

Second one:

SELECT id,column1,column2 FROM `table1` WHERE column1 LIKE %string1% 
OR column1 LIKE %string2% OR column1 LIKE %string3% OR column2 LIKE %string1% 
OR column2 LIKE %string2% OR column2 LIKE %string3% OR column3 LIKE %string1% OR
column3 LIKE %string2% OR column3 LIKE %string3%  ORDER BY column2 ASC LIMIT 10

Upvotes: 1

Views: 65

Answers (3)

haltabush
haltabush

Reputation: 4538

MATCH...REPLACE will be faster to read, but longer to insert. You'll have to create an index on it. You'll also have a lot of requirements if you want to use that (it works only on MyISAM tables)

You should read this : http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

And here are the restrictions I was speaking about : http://dev.mysql.com/doc/refman/5.0/en/fulltext-restrictions.html

Upvotes: 2

Dave Halter
Dave Halter

Reputation: 16335

Match against is a fulltext search. If you created a fulltext search index for MySQL it will be much faster than the LIKE '%...%', because in this case there will be no index.

The reason why there cannot be any index on '%...%', is because it is simply not fast to create an index on every letter of your strings.

Also those two query's have a different function, the fulltext index scans for words and is only usable with words. You cannot search a string "emil" with "match against('mi' in boolean mode). That will not work. Of course with like this works.

Upvotes: 3

Marc B
Marc B

Reputation: 360882

like '%foo%' type matches make it impossible to use indexes, and the DB will have to do a full table scan every time. The fulltext version SHOULD be far faster.

Upvotes: 0

Related Questions