EXPLAIN
From Lianjapedia
Contents
Purpose
Display information about the optimization and result count of an SQL SELECT statement
Syntax
EXPLAIN <SELECT statement>
See Also
CLOSE ALTERNATE, CREATE INDEX, INDEX, PROFILE, SELECT, SET ALTERNATE, SET CONSOLE, SET EXPLAIN, SQLCNT
Description
When an SQL SELECT statement is preceded by the EXPLAIN command, information is displayed about the optimization that would be carried out on that SELECT statement and the number of results that it would return. Analysis of the EXPLAIN output can be used by the Developer to target index creation for the maximum performance of SELECT statements.
Issuing SET EXPLAIN ON causes all executed SQL SELECT statements to output their EXPLAIN execution plan to the console.
Example
open database southwind explain select * from employees where hiredate < gomonth(date(),-36)
******************************************************************************** Explaining execution plan for command: select * from employees where hiredate < gomonth(date(),-36) -------------------------------------------------------------------------------- SET EXCLUSIVE ON SET NETWORKSHARE OFF SET SMARTQUERY OFF SET STRCOMPARE ON (Case insensitive string comparisons with padding) SET DCACHE ON (Table page cache) SET DCACHEBLOCKSIZE TO 8192 (Size of table cache pages) SET DCACHESIZE TO 10 (Number of table pages to cache) SET ICACHE TO 50 (Index node cache) SET SQLCACHE ON (SQL Query cache) SET SQLCACHESIZE TO 65536 (Size of SQL Query cache extents) SET SQLBUFFERSIZE TO 1024 (Number of SQL write cache buffers) WHERE condition for table 'employees' could not be optimized Total I/O read operations was 0 Total I/O read size was 0.0000MB Total I/O write size was 0.0000MB Total SEEK operations performed was 0 Total SEEK operations failed was 0 Total locks performed 0 Total rlocks performed was 0 Total unlocks performed was 0 (0 active) Total I/O read cache hits was 11 Total I/O read cache misses was 0 Total I/O index reads was 0 Total I/O index cache hits was 0 Total I/O index cache misses was 0 9 records selected in 27ms
explain select * from employees inner join orders on employees.employeeid = orders.employeeid
******************************************************************************** Explaining execution plan for command: select * from employees inner join orders on employees.employeeid = orders.employeeid -------------------------------------------------------------------------------- Optimized JOIN to table 'orders' using index tag 'EMPLOYEEID' keytype=N keylength=8 SET EXCLUSIVE ON SET NETWORKSHARE OFF SET SMARTQUERY OFF SET STRCOMPARE ON (Case insensitive string comparisons with padding) SET DCACHE ON (Table page cache) SET DCACHEBLOCKSIZE TO 8192 (Size of table cache pages) SET DCACHESIZE TO 10 (Number of table pages to cache) SET ICACHE TO 50 (Index node cache) SET SQLCACHE ON (SQL Query cache) SET SQLCACHESIZE TO 65536 (Size of SQL Query cache extents) SET SQLBUFFERSIZE TO 1024 (Number of SQL write cache buffers) Performing join Parent table 'employees' has 12 records, table size is 128.000KB, DCACHE is ON, DCACHEBLOCKSIZE 8451, record size 313, records per page 27 Child table 'orders' has 834 records, table size is 256.000KB, DCACHE is ON, DCACHEBLOCKSIZE 8451, record size 251, records per page 33 Processing parent table 'employees' sequentially Total I/O read operations was 215 Total I/O read size was 1.7296MB Total I/O write size was 0.0000MB Total SEEK operations performed was 11 Total SEEK operations failed was 0 Total locks performed 0 Total rlocks performed was 0 Total unlocks performed was 0 (0 active) Total I/O read cache hits was 846 Total I/O read cache misses was 214 Total I/O index reads was 8 Total I/O index cache hits was 32 Total I/O index cache misses was 8 822 records selected in 75ms
// Cause all SQL SELECT statements to use EXPLAIN set explain on select * from employees where hiredate < gomonth(date(),-36) select * from employees inner join orders on employees.employeeid = orders.employeeid
EXPLAIN execution plans output as above
// Send EXPLAIN output to a text file set alternate to explain set console off set alternate on explain select * from employees inner join orders on employees.employeeid = orders.employeeid close alternate
EXPLAIN execution plans output to explain.txt