Obtaining Execution Plan Information for a Named Connection 8.8.4 Obtaining Execution Plan Information for a Named Connection 8.8.5 Estimating Query Performance 1779 In most cases, you can estimate query performance by counting disk seeks. For small tables, you can usually find a row in one disk seek (because the index is probably cached). For bigger tables, you can estimate that, using B-tree indexes, you need this many seeks to find a row:  log( row_count ) / log( index_block_length  / 3 * 2 / ( index_length  +  data_pointer_length )) + 1 . In MySQL, an index block is usually 1,024 bytes and the data pointer is usually four bytes. For a 500,000-row table with a key value length of three bytes (the size of  MEDIUMINT ), the formula indicates log(500,000)/log(1024/3*2/(3+4)) + 1  =  4  seeks. This index would require storage of about 500,000 * 7 * 3/2 = 5.2MB (assuming a typical index buffer fill ratio of 2/3), so you probably have much of the index in memory and so need only one or two calls to read data to find the row. If the named connection is executing a statement that is not explainable, an error occurs. For example, you cannot name the connection identifier for your current session because  EXPLAIN  is not explainable: If the named connection is executing an explainable statement, the output is what you would obtain by using  EXPLAIN  on the statement itself. If the named connection is not executing a statement, the result is empty. Otherwise,  EXPLAIN FOR CONNECTION  applies only if the statement being executed in the named connection is explainable. This includes  SELECT ,  DELETE ,  INSERT ,  REPLACE , and  UPDATE . (However,  EXPLAIN FOR CONNECTION does not work for prepared statements, even prepared statements of those types.) connection_id  is the connection identifier, as obtained from the  INFORMATION_SCHEMA PROCESSLIST table or the  SHOW PROCESSLIST  statement. If you have the  PROCESS  privilege, you can specify the identifier for any connection. Otherwise, you can specify the identifier only for your own connections. In all cases, you must have sufficient privileges to explain the query on the specified connection. EXPLAIN FOR CONNECTION  returns the  EXPLAIN  information that is currently being used to execute a query in a given connection. Because of changes to data (and supporting statistics) it may produce a different result from running  EXPLAIN  on the equivalent query text. This difference in behavior can be useful in diagnosing more transient performance problems. For example, if you are running a statement in one session that is taking a long time to complete, using  EXPLAIN FOR CONNECTION  in another session may yield useful information about the cause of the delay. EXPLAIN [ options ] FOR CONNECTION  connection_id ; To obtain the execution plan for an explainable statement executing in a named connection, use this statement: The  Com_explain_other  status variable indicates the number of  EXPLAIN FOR CONNECTION statements executed. mysql>  SELECT CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ | 373 | +-----------------+ 1 row in set (0.00 sec) mysql>  EXPLAIN FOR CONNECTION 373; ERROR 1889 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE