mysql query plans

Question

I have a list of select statements and I wish to find out their query plans. Is there an easier way instead of having to prefix each one with explain?

In Sybase you can do this by setting a flag - just wonder if there is an equivalent?

Answer 1

You can abuse the slow query log for this:

Set long_query_time = 0, this will force MySQL to log all queries in the slow query log.

Now set where you want the log to appear, see: http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html Make sure you let the slow query log put your queries in a table, this is the default in 5.1+

Now write a stored procedure like so:

DELIMITER $$

CREATE PROCEDURE run_explain_on__the_slow_query_log(IN which_db VARCHAR(512))
BEGIN
  DECLARE sql VARCHAR(10000);
  DECLARE done INTEGER DEFAULT 0;
  DECLARE cursor1 CURSOR FOR SELECT sql_text FROM mysql.slow_log 
                             WHERE sql_text LIKE 'SELECT%'
                               AND (db = which_db OR which_db IS NULL); 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cursor 1;
  WHILE NOT (done) DO 
    FETCH cursor1 INTO sql;
    SET sql = CONCAT('EXPLAIN EXTENDED ',sql);
    PREPARE stmt FROM sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END WHILE;   
END $$

DELIMITER ; 

You may need to tweak it a bit, here's the create statement of the slow_log table.

CREATE TABLE mysql.slow_log(
  start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  user_host MEDIUMTEXT NOT NULL,
  query_time TIME NOT NULL,
  lock_time TIME NOT NULL,
  rows_sent INT(11) NOT NULL,
  rows_examined INT(11) NOT NULL,
  db VARCHAR(512) NOT NULL,
  last_insert_id INT(11) NOT NULL,
  insert_id INT(11) NOT NULL,
  server_id INT(10) UNSIGNED NOT NULL,
  sql_text MEDIUMTEXT NOT NULL
)
ENGINE = CSV
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'Slow log';