1

This question already has an answer here:

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;

marked as duplicate by ypercubeᵀᴹ mysql Jun 12 '17 at 22:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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.

1

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

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

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.

  • Rolando, thanks for the reply but I am not sure if I understand your options. My goal is to know how the same query would perform without indexes. Wouldn't changing the query in any way void the benchmark? – Lando Mar 20 '15 at 22:36
  • I don't think WHERE 1=1 will turn off an index. (It could be used to fool the Query cache, but SQL_NO_CACHE is taking care of that.) – Rick James Mar 21 '15 at 1:06
  • @RickJames For InnoDB, you are right in that aspect. The query should do a full pass through the PrimaryKey/Clustered Index anyway, even with my OPTION #1. Your approach should be the same, just more work sculpting the index names into the SELECT using information_schema. – RolandoMySQLDBA Mar 21 '15 at 1:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.