CODEWITHSUNDEEP
mysqlsqlperformance
Biwu
Biwu

Reputation: 103

Speed up MySQL string LIKE query

I have a table with (at the moment) more than 200.000 rows. Every row is a page on my site, and I use Friendly Urls, like site.com/itemName.

On my SlowQueries log I have that query, repeated over 1500 times, with 1.5s loading time.

This is the query:

SELECT * FROM table WHERE `name` LIKE 'S' ORDER BY id DESC LIMIT 1

Table type is MyISAM. The field is varchar 250 with FULLTEXT index on it. Also, I've got this MySQLTuner results:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.61-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 191M (Tables: 4)
[--] Data in InnoDB tables: 48K (Tables: 1)
[!!] Total fragmented tables: 1

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 4h 56m 2s (3M q [34.272 qps], 37K conn, TX: 36B, RX: 3B)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 290.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 705.2M (68% of installed RAM)
[OK] Slow queries: 0% (2K/3M)
[OK] Highest usage of available connections: 8% (13/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/63.3M
[OK] Key buffer hit rate: 100.0% (581M cached / 182K reads)
[!!] Query cache efficiency: 4.7% (146K cached / 3M selects)
[OK] Query cache prunes per day: 0
[!!] Sorts requiring temporary tables: 32% (8K temp sorts / 26K sorts)
[OK] Temporary tables created on disk: 5% (3 on disk / 53 total)
[OK] Thread cache hit rate: 99% (13 created / 37K connections)
[OK] Table cache hit rate: 53% (33 open / 62 opened)
[OK] Open file limit used: 5% (59/1K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 48.0K/8.0M

Upvotes: 0

Views: 833

Answers (2)

Álvaro González
Álvaro González

Reputation: 146450

Some random thoughts:

  • WHERE name LIKE 'S' is identical to WHERE name='S'. It's possible that MySQL is smart enough to generate the same execution plan (I haven't checked) but it's confusing.
  • FULLTEXT indexes are used for full-text searches. You aren't doing one, so the index is useless for this query. You should create a regular index.
  • Your server settings are probably less informative than the query execution plan. Preppend EXPLAIN or EXPLAIN EXTENDED to the SQL code to get it.

Upvotes: 2

tadman
tadman

Reputation: 211590

You probably need to use the fulltext searching functions to take advantage of those indexes. As far as I know, LIKE cannot use them automatically.

Upvotes: 1

Related Questions