mysql like performance boost

mysql sql-like
user121196 picture user121196 · Mar 20, 2010 · Viewed 21.8k times · Source

is there anyway to speed up mysql like operator performance if wild card is involved? eg. like '%test%'

Answer

Cal picture Cal · Mar 20, 2010

MySQL can use an index if your query looks like foo LIKE 'abc%' or foo LIKE 'abc%def%'. It can use the index for any portion or the string before the first wildcard. If you need to match a word anywhere within a string, you might want to consider using FULLTEXT indexes instead.

More detail on indexes: http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

Full text search: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Related questions

How can I search (case-insensitive) in a column using LIKE wildcard?

I looked around some and didn't find what I was after so here goes. SELECT * FROM trees WHERE trees.`title` LIKE '%elm%' This works fine, but not if the tree is named Elm or ELM etc... How do …

Read More
MySQL Like multiple values

I have this MySQL query. I have database fields with this contents sports,shopping,pool,pc,games shopping,pool,pc,games sports,pub,swimming, pool, pc, games Why does this like query does not work? I need the fields with …

Read More
MySQL: NOT LIKE

I have these text in my db, categories_posts categories_news posts_add news_add And I don't want to select the rows with categories, I use a query something like this, SELECT * FROM developer_configurations_cms WHERE developer_configurations_…

Read More