Performance of LIKE queries on multmillion row tables, MySQL

mysql database performance processing-efficiency
roozbubu picture roozbubu · Jul 10, 2012 · Viewed 21.9k times · Source

From anybody with real experience, how do LIKE queries perform in MySQL on multi-million row tables, in terms of speed and efficiency, if the field has a plain INDEX?

Is there a better alternative (that doesn't filter results out, like the FULLTEXT 50% rule) for perform database field searches on multi-million row tables?

EXAMPLE:

Schema (comments table)

id (PRIMARY) title(INDEX) content time stamp

Query

SELECT * FROM 'comments' WHERE 'title' LIKE '%query%'

Answer

Eric J. picture Eric J. · Jul 10, 2012

LIKE will do a full table scan if you have a % at the start of the pattern.

You can use FULLTEXT in Boolean (rather than natural language) mode to avoid the 50% rule.

Boolean full-text searches have these characteristics:

They do not use the 50% threshold.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

Related questions

MyISAM versus InnoDB

I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. …

Read More
MySql views performance

If you are going down the road of using views, how can you ensure good performance? Or is it better not to use views in the first place and just incorporate the equivalent into your select statements?

Read More
Database/SQL: How to store longitude/latitude data?

Performance question ... I have a database of houses that have geolocation data (longitude & latitude). What I want to do is find the best way to store the locational data in my MySQL (v5.0.24a) using InnoDB database-engine so that …

Read More