1

I've been benchmarking several queries and learning about indexes by using the explain function, profiling and the SQL_NO_CACHE option.

My usual benchmarking session goes something like this:

SET PROFILING=1;
SELECT SQL_NO_CACHE field_a, field_b FROM table WHERE xxx;
SHOW PROFILES;

Some times after adding indexes, I want to know how the query would perform without any index optimization; so my options so far are to either delete the related indexes or use explain to check what indexes are in use and hint MySQL what indexes to ignore;

EXPLAIN SELECT SQL_NO_CACHE field_a, field_b FROM table WHERE xxx;
SELECT SQL_NO_CACHE IGNORE INDEX (field_a) field_a, field_b FROM table WHERE xxx;
SHOW PROFILES;

Is there a similar option to tell MySQL not to use any index so I can get the query duration without any index optimization. Something like this:

SELECT SQL_NO_CACHE SQL_NO_INDEXES field_a, field_b FROM table WHERE xxx;
Community
  • 1
Lando
  • 123
  • 1
  • 7

3 Answers3

2

After a table name add USE INDEX() without arguments. That will make MySQL to ignore all index in the table.

Example:

SELECT SQL_NO_CACHE field_a, field_b FROM table USE INDEX() WHERE xxx;

Sorry for my english.

august0490
  • 121
  • 4
1

... FROM tbl IGNORE INDEX (idx1, idx2, ...) ...

Unfortunately, you need to know the names of all the indexes.

Rick James
  • 80,479
  • 5
  • 52
  • 119
1

If you could change the WHERE clause for the sake of the benchmark, do one of two things:

OPTION #1 : Table Scan

SET PROFILING=1;
SELECT SQL_NO_CACHE field_a, field_b FROM table;
SELECT SQL_NO_CACHE field_a, field_b FROM table;
SELECT SQL_NO_CACHE field_a, field_b FROM table;
SHOW PROFILES;

OPTION #2 : Change WHERE xxx to WHERE 1=1

SET PROFILING=1;
SELECT SQL_NO_CACHE field_a, field_b FROM table WHERE 1=1;
SELECT SQL_NO_CACHE field_a, field_b FROM table WHERE 1=1;
SELECT SQL_NO_CACHE field_a, field_b FROM table WHERE 1=1;
SHOW PROFILES;

This option might be slightly slower.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536