| Bug #19836 | Dependent subquery in on clause is not optimized effectively. | ||
|---|---|---|---|
| Submitted: | 16 May 2006 2:03 | Modified: | 14 Dec 2006 10:16 |
| Reporter: | Gökhan Demir | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | all | OS: | Any (all) |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[16 May 2006 2:13]
Gökhan Demir
I have uploaded the bug-data-19836.tar.bz2 to mysql ftp side.
[4 Jul 2006 18:12]
MySQL Verification Team
Verified as described on Linux Suse 10.
[22 Aug 2006 14:33]
Cristiano Contin
I've the same problem; it looks that using views workaround the problem
[14 Dec 2006 10:16]
Georgi Kodinov
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely to be the same. Because of this, we hope you add your comments to the original bug instead. Thank you for your interest in MySQL. This is actually a duplicate of bug #18465. MySQL tries to avoid re-optimization at execution time. And it can't decide on optimization time that certain subquery will be subject to range optimization using values from the outer context because these constants will vary for each execution and may cause great inefficiency of the range access. So unlike joins subqueries are not subject to range optimization when values that may change at execution time are involved. This may be extended by the ongoing subquery optimization additions that will be included into a future release.
Description: Dependent subquery in on clause is not optimized effectively by MySQL. A column from the same join, -which I believe has to be considered as constant- is not used by the dependent subquery's index, causing a really slow response time, especially when data is big. How to repeat: use test; drop table if exists Statement; drop table if exists CurrDailyVal; create table Statement ( id int not null, xdate datetime not null, cur char(3) not null, amt numeric(15,4) not null, primary key (id) ) engine = innoDB; create table CurrDailyVal ( cur1 char(3) not null, cur2 char(3) not null, xdate datetime not null, val numeric(15,4) not null, primary key (cur1, cur2, xdate) ) engine = innoDB; # the data files are attached to this bug case. load data infile 'Statement.txt' into table Statement fields terminated by '\t'; load data infile 'CurrDailyVal.txt' into table CurrDailyVal fields terminated by '\t'; describe select count(*) from Statement T join CurrDailyVal Q on Q.cur1 = T.cur and Q.cur2 = "EUR" and Q.xdate = ( select max(xdate) from CurrDailyVal where cur1 = Q.cur1 and cur2 = "EUR" and xdate <= T.xdate ); /** * Produces the following output on my 4.1.19 installations (both on Linux and Windows). * * id select_type table type possible_keys key key_len ref rows Extra * ------ ------------------ ------------ ------ ------------- ------- ------- ----------------- ------ ------------------------ * 1 PRIMARY T ALL (NULL) (NULL) (NULL) (NULL) 173039 * 1 PRIMARY Q ref PRIMARY PRIMARY 6 test.T.cur,const 1 Using where; Using index * 2 DEPENDENT SUBQUERY CurrDailyVal ref PRIMARY PRIMARY 6 test.Q.cur1,const 64 Using where; Using index */ Suggested fix: I don't know what to do, other than reconsider my database design to replace that kind of select statements.