Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
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;
|
Attachments
Issue Links
- relates to
-
MDEV-165 MWL#182: Explain running statements: finish coding, submit for review
-
- Closed
-
Activity
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.