mysql speed up search query

Question

I have an article table with 2 columns

Id INT(4) PK autoincrement
Description VARCHAR(250)
(and more columns)

This table contains 500.000 records and is a INNODB table. Now I want to search an article like this:

SELECT COUNT(*) FROM article (description like '%cannon%');

It takes almost a second to execute ..

What can I to to make this faster?

I have alread an index on the Description column

Answer 1

You should consider adding fulltext index on description:

http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

And then use:

SELECT 
    COUNT(*) 
FROM article 
WHERE MATCH (description) AGAINST ('cannon' WITH QUERY EXPANSION);

Answer 2

Queries with like '%cannon%' are very hard to optimize. No, indexes can't help you. Maybe full-text search can help you.