[MDEV-410] EXPLAIN shows type=range, while SHOW EXPLAIN and userstat show full table scan is used Created: 2012-07-24  Updated: 2012-07-25  Resolved: 2012-07-25

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: 10.0.0

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-165 MWL#182: Explain running statements: ... Closed

 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;



 Comments   
Comment by Sergei Petrunia [ 2012-07-25 ]

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;
-------------------------------------------+

Table_schema Table_name Index_name Rows_read

-------------------------------------------+

j12 t2a b 1

-------------------------------------------+
1 row in set (0.01 sec)

MariaDB [j12]> show table_statistics;
--------------------------------------------------------------------

Table_schema Table_name Rows_read Rows_changed Rows_changed_x_#indexes

--------------------------------------------------------------------

j12 t2a 1 0 0
j12 t2 2 0 0
j12 t1 2 0 0

--------------------------------------------------------------------
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.

Comment by Sergei Petrunia [ 2012-07-25 ]

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))))
{

Comment by Sergei Petrunia [ 2012-07-25 ]

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

Comment by Sergei Petrunia [ 2012-07-25 ]

Verified that the problem with EXPLAIN being inconsistent with userstat profiling exists on the current mariadb-5.5 ( sanja@montyprogram.com-20120724145006-17q5hre8fwp1toql )

Comment by Sergei Petrunia [ 2012-07-25 ]

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

Generated at Thu Feb 08 06:28:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.