CODEWITHSUNDEEP
mysqldatabasefull-text-searchquery-optimizationsql-like
oyvey
oyvey

Reputation: 601

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 a varchar(250) field. There are currently 356,187 records in the synonym database table. Data: 21 MB. Indexes: 23 MB. Total size: 45 MB. Bytes per row: 67.

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

The truth is that several of the fields in my table are probably extraneous. If I would, say, cut the number of bytes per row in half, by eliminating unnecessary fields, would that make the search twice as fast?

Thanks in advance.

Upvotes: 3

Views: 2101

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53830

If ak is a word, then a FULLTEXT index would work (if you adjust the minimum word length, see below).

So, a search for 'ak' with a FULLTEXT index would match on this:

  • 'This ak that.'
  • 'AK'
  • 'AK. Whatever.'
  • 'Whatever. AK.'

However, it would not match this:

  • 'BAK'
  • 'AKT'

There have to be word boundaries in order to match.

The default minimum word length for a FULLTEXT search is 4 characters. So, you still couldn't do a FULLTEXT search for 'ak' because it's too short. You could lower the minimum word length setting, but you'll end up with 'the', 'and' and all the other three and shorter letter words that you don't want cluttering your FULLTEXT index.

Searching with LIKE might be your only viable option. When you use a leading wildcard ('%ak'), MySQL cannot utilize an index to locate the record. It has to scan all rows. Still, it will use the index to scan if you have a covering index.

So, for your query:

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

If you have a multi-column, covering, index on (name, synonym_group), it will actually still use the index to answer the query, but not in the traditional sense. MySQL will scan the index, which is generally faster than scanning the actual table data (table scan). Plus, the ideal system has enough RAM to store all indexes in RAM, so it's just scanning memory instead of disk.

So, with the covering index, the size of your rows would have no effect.

Without the covering index, the size of your rows would affect the scan speed, since the disk has to move farther.

If you do end up doing table scans, you'll want your table to be defragmented, and preferably have fixed length rows (CHAR instead of VARCHAR).

Upvotes: 4

McGarnagle
McGarnagle

Reputation: 102723

As juergen d mentions, the search with % at the beginning can't use your index, and has to scan the entire table (bad and will only get worse as the table size increases). Decreasing the number of columns will likely not help, because the real CPU drain is cycling through the string in every row.

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

Upvotes: 3

juergen d
juergen d

Reputation: 204746

If you use LIKE operator starting with % then no index will be used for your select.

So, yes that time is normal.

Upvotes: 5

Related Questions