MySQL Bugs: #14295: very slow 'explain'

Bug #14295	very slow 'explain'
Submitted:	25 Oct 2005 14:06	Modified:	31 Aug 2012 16:06
Reporter:	Aleksey Kishkin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1 and above	OS:	Linux (linux)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
the query 

select
        cntrycode,
        count(*) as numcust,
        sum(c_acctbal) as totacctbal
from
        (
                select
                        substr(c_phone, 1, 2) as cntrycode,
                        c_acctbal
                from
                        customer
                where
                        substr(c_phone, 1, 2) in
                                ('24', '20', '14', '23', '15', '22', '25')
                        and c_acctbal > (
                                select
                                        avg(c_acctbal)
                                from
                                        customer
                                where
                                        c_acctbal > 0.00
                                        and substr(c_phone, 1, 2) in
                                                ('24', '20', '14', '23', '15', '22', '25')
                        )
                        and not exists (
                                select
                                        *
                                from
                                        orders
                                where
                                        o_custkey = c_custkey
                        )
        ) as vip
group by
        cntrycode
order by
        cntrycode;

takes too much time for 'explain' . On 100M data, and P-IV 2.4GHz computer it takes about 4 minutes just for 'explain'. When I increased data size to 1G, 'explain' time became more than 7000 seconds - more than 2 hours.

I think it's not acceptable for practiacal reason.

How to repeat:
1) download  data and fill script from bug#14292 
2) fill tables
3) run attached script
4) look at executeion time.

test query

Attachment: 22.sql (text/plain), 1.31 KiB.

This is a well-known problem: EXPLAIN command triggers materialization of all derived tables. The problem will be solved when we re-architecture the implementation of derived tables.

This bug will be fixed by WL#5274 Postpone materialization of views/subqueries in FROM clause.

Manyi

Implementation of the subquery materialization feature is mentioned in the 5.6.3 changelog.