0

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

Ruutert
  • 395
  • 1
  • 7
  • 18
  • 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 Jan 21 '13 at 12:12
  • 2
    index on description does not help – ajreal Jan 21 '13 at 12:13
  • 3
    `like '%cannon%'` kills the index. – John Woo Jan 21 '13 at 12:13
  • Possible duplicate: http://stackoverflow.com/questions/2081998/optimization-of-mysql-search-using-like-and-wildcards – Andreas Fester Jan 21 '13 at 12:15
  • The best optimization possible is if you use `COUNT(1)` instead of `COUNT(*)`. The `%canon%` negates any other kind of optimization. – hjpotter92 Jan 21 '13 at 12:15
  • yes, if you are using 5.6, try fulltext search -- http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html – ajreal Jan 21 '13 at 12:16
  • if you have some trick to divide description filed that you can improve you table struct: for example: `description = date , about , subject, bla .... ` – Grijesh Chauhan Jan 21 '13 at 12:17

2 Answers2

2

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);
povilasp
  • 2,386
  • 1
  • 22
  • 36
1

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

Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68