Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: 10.0.0
-
Component/s: None
-
Labels:None
Description
For the following query
SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 WHERE SLEEP(0.2) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 )
usual EXPLAIN produces
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 range b b 5 NULL 2 Using index condition; Using where 1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
while SHOW EXPLAIN says
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL b NULL NULL NULL 2 Using where 1 PRIMARY t3 ref d d 5 test.t2.b 2 Using where; Using index 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
As always, SLEEP in the query is not essential, it just makes the query a bit longer and allows to catch it by SHOW EXPLAIN.
bzr version-info (5.5-show-explain-test1)
revision-id: psergey@askmonty.org-20120719215203-m2p9cbqb37n0th7n date: 2012-07-20 01:52:03 +0400 build-date: 2012-07-24 04:43:23 +0400 revno: 3456
Test case:
CREATE TABLE t1 (a VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM;
INSERT INTO t1 VALUES ('USA');
CREATE TABLE t2 (b INT, c VARCHAR(52), KEY(b)) ENGINE=MyISAM;
INSERT INTO t2 VALUES (3813,'United States'),(3940,'Russia');
CREATE TABLE t3 (d INT, KEY(d)) ENGINE=MyISAM;
INSERT INTO t3 VALUES (12),(22),(9),(45);
EXPLAIN
SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1
WHERE SLEEP(0.2) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
--echo #---------------
--echo # SHOW EXPLAIN output:
--connect (con1,localhost,root,,)
--let $con_id = `SELECT CONNECTION_ID()`
--let $trials = 50
--disable_query_log
while ($trials)
{
--dec $trials
--let $run = 1000
send
SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1
WHERE SLEEP(0.2) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2 );
--connection default
while ($run)
{
--error 0,1932
eval SHOW EXPLAIN FOR $con_id;
--dec $run
if (!$mysql_errno)
{
--let $run = 0
--let $trials = 0
--let $found = 1
}
}
--disable_result_log
--connection con1
--reap
--enable_result_log
}
if (!$found)
{
--echo ########### Could not catch the query by SHOW EXPLAIN, try again #############
}
DROP TABLE t1, t2, t3;
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-165 MWL#182: Explain running statements: finish coding, submit for review
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Debugged SELECT and its EXPLAIN concurrently. The difference is in make_join_statistics(), here:
/*
We plan to scan all rows.
Check again if we should use an index.
We could have used an column from a previous table in
the index if we are using limit and this is the first table
*/
if (!tab->table->is_filled_at_execution() &&
((cond && (!tab->keys.is_subset(tab->const_keys) && i > 0)) ||
(!tab->const_keys.is_clear_all() && i == join->const_tables &&
join->unit->select_limit_cnt <
join->best_positions[i].records_read &&
!(join->select_options & OPTION_FOUND_ROWS))))
{
The difference comes from different value of tab->const_keys(), which comes from update_ref_and_keys() , from add_key_fields() call for this part of the WHERE condtion:
(`j14`.`t2`.`b` = (select min(`j14`.`t2`.`b`) from `j14`.`t2`))
Here, the right part is an Item_singlerow_subselect, and it has item->const_item() == FALSE for the EXPLAIN and TRUE for the SELECT.
the difference comes from different value of their Item::const_item_cache
Verified that the problem with EXPLAIN being inconsistent with userstat profiling exists on the current mariadb-5.5 ( sanja@montyprogram.com-20120724145006-17q5hre8fwp1toql )
Figured out this is a problem in EXPLAIN, not in SHOW EXPLAIN.
Debugged, fixed, pushed into 5.5
Merged into the 5.5-show-explain-test1 tree
It seems, SHOW EXPLAIN's output is correct, and EXPLAIN is wrong.
Let's create a copy of t2 so that accesses to t2-outside-the-subquery and t2-inside-the-subquery are counted separately:
create table t2a like t2;
insert into t2a select * from t2;
Then I run:
SELECT MIN(b) FROM ( SELECT * FROM t1, t2, t3 WHERE d = b ) AS alias1 WHERE SLEEP(0.2) OR c < 'p' OR b = ( SELECT MIN(b) FROM t2a );
and then I see:
MariaDB [j12]> show index_statistics;
-------------
------------------------------+-------------
------------------------------+-------------
------------------------------+1 row in set (0.01 sec)
MariaDB [j12]> show table_statistics;
-------------
--------------------------------------------------------------------
--------------------------------------------------------------------
-------------------------------------------------------3 rows in set (0.01 sec)
As one can see, there were 2 rows read from table t2, and they were not read through an index.