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

There is a few things that you could do, one would be to add a primary key to the table to ensure that it can be indexed correctly, and then add indexes on the table to speed up the query. Also, SELECT COUNT(*) FROM article WHERE description LIKE '%cannon%'; — DarkMantis
– DarkMantis, Commented Jan 21, 2013 at 12:12
The best optimization possible is if you use COUNT(1) instead of COUNT(*). The %canon% negates any other kind of optimization. — hjpotter92
– hjpotter92, Commented Jan 21, 2013 at 12:15

povilasp · Accepted Answer · 2013-01-22 06:35:58Z

2

You should consider adding fulltext index on description:

And then use:

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

answered Jan 21, 2013 at 12:16

povilasp

2,4061 gold badge24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

the storage engine is innodb

my dear, the support of full-text search is 5.6 onwards, yet, you paste a documentation of 5.0

Hamlet Hakobyan · Accepted Answer · 2013-01-21 12:14:23Z

1

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

answered Jan 21, 2013 at 12:14

Hamlet Hakobyan

33.5k6 gold badges55 silver badges71 bronze badges