Testing performance of queries in mysql

Assuming that you can not optimize the LIKE operation itself, you should try to optimize the base query without them minimizing number of rows that should be checked.

Some things that might be useful for that:

rows column in EXPLAIN SELECT ... result. Then,

mysql> set profiling=1;mysql> select sql_no_cache * from mytable; ...mysql> show profile;+--------------------+----------+| Status             | Duration |+--------------------+----------+| starting           | 0.000063 || Opening tables     | 0.000009 || System lock        | 0.000002 || Table lock         | 0.000005 || init               | 0.000012 || optimizing         | 0.000002 || statistics         | 0.000007 || preparing          | 0.000005 || executing          | 0.000001 || Sending data       | 0.001309 || end                | 0.000003 || query end          | 0.000001 || freeing items      | 0.000016 || logging slow query | 0.000001 || cleaning up        | 0.000001 |+--------------------+----------+15 rows in set (0.00 sec)

Then,

mysql> FLUSH STATUS;mysql> select sql_no_cache * from mytable;...mysql> SHOW SESSION STATUS LIKE 'Select%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| Select_full_join       | 0     || Select_full_range_join | 0     || Select_range           | 0     || Select_range_check     | 0     || Select_scan            | 1     |+------------------------+-------+5 rows in set (0.00 sec)

And another interesting value is last_query_cost, which shows how expensive the optimizer estimated the query (the value is the number of random page reads):

mysql> SHOW STATUS LIKE 'last_query_cost';+-----------------+-------------+| Variable_name   | Value       |+-----------------+-------------+| Last_query_cost | 2635.399000 |+-----------------+-------------+1 row in set (0.00 sec)

MySQL documentation is your friend.

Cited from this page: SQL_NO_CACHE options affect caching of query results in the query cache. If your table is quite small, it is possible, that the table itself is already cached. Since you just avoid caching of the results and not the tables you get the described behavior sometimes. So, as told in the other postings, you should flush your tables in between the queries.

As the linked article suggests, use FLUSH TABLES between test runs to reset as much as you can (notably the query cache).

Shouldn't your testing take into account that InnoDB will itself have different states during actual performance, such that you become interested in aggregate performance over multiple trials? How "real" is your performance testing going to be if you want to reset InnoDB for every trial? The query you reject because it performs poorly immediately after restart might be far and away the best query after InnoDB has warmed up a little bit.

If I were you, I'd focus on what the query optimizer is doing separately from InnoDB's performance. There's much written about how to tune InnoDB, but it helps to have good queries to start.

You could also try measuring performance with equivalent MyISAM tables, where FLUSH TABLES really will reset you to a mostly-identical starting point.

Have you tried turning query caching off altogether? Even with SQL_NO_CACHE, there's about a 3% penalty just having the query cache on.