Bug #3111 EXPLAIN materializes Derived table
Submitted: 8 Mar 2004 16:53 Modified: 8 Mar 2004 22:43
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 OS:Any (all)
Assigned to: CPU Architecture:Any

[8 Mar 2004 16:53] Peter Zaitsev 
Description:
EXPLAIN Materializes  derived table in order to complete, This makes it unusable
for identifying problematic queries as you will never see it to complete.

This is especially strange for single derived table queries (majority) where the outer select does not have much choices, other than materialized table scan anyway.

How to repeat:
CREATE TABLE dt (
  d double default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table 'dt'
--

INSERT INTO dt VALUES (1.11111111111111e+54),(1.11111111111111e+54),(1.11111111111111e+54),(1),(1),(1),(1),(1),(1),(1);

explain select d from (select t1.d as d from dt,dt t1, dt t2, dt t3, dt t4, dt t5, dt t6, dt t7 where t7.d<5) z;
[8 Mar 2004 22:43] Oleksandr Byelkin 
Thank you for bugreport, but this is quite expected behaviour according to 
internal.texi. 
Also MySQL have to have all underlay tables to make optimisation phase. 
 
As far as subquery in the FROM clause (it is correct term for "materializes 
Derived table" from MySQL documentation) can't be correlated (for now), and 
perform independently, I can recomment to run separate EXPLAIN for subquery to 
detect problem in it.