Geek asks and answers

How fast should my Mysql LIKE statement be?

The following MYSQL statement takes 0.577251 seconds:

SELECT synonym_group FROM synonym WHERE name LIKE '%ak%'

Name is the varchar field (250). There are currently 356,187 entries in the synonyms database table. Data: 21 MB. Indexes: 23 MB. Total size: 45 MB. Bytes per line: 67.

So this is 0.577251 seconds and then a reasonable time? If not, what should I do? I read several topics on this type of question, and the main solution that I see is to use something like sphinx.

In truth, some of the fields in my table are probably extraneous. If I, say, reduce the number of bytes by one line in half, eliminating unnecessary fields, will this make the search twice as fast?

Thanks in advance.

+3
source share
3

ak - , FULLTEXT ( , . ).

, 'ak' FULLTEXT :

  • ' ak, .'
  • ''
  • '. .
  • '. ".

:

  • ''
  • 'AKT'

.

FULLTEXT 4 . , FULLTEXT "ak", . , "the", "and" , , FULLTEXT.

LIKE . ('%ak'), MySQL . . , , .

, :

SELECT synonym_group FROM synonym WHERE name LIKE '%ak%'

, (name, synonym_group), , . MySQL , , , , ( ). , , .

, .

, .

, , , , (CHAR VARCHAR).

+4

LIKE, %, .

, , .

+5

As juergen d mentions, searching with% at the beginning cannot use your index and should scan the entire table (bad and will only get worse as the size of the table increases). Reducing the number of columns will most likely not help, because a real CPU leak moves cyclically across the row in each row.

In this case, you should use full-text search and index: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

+3
source

More articles:


All Articles