Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
None
-
None
Description
If to create and populate tables t1,t2 with the commands
CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
INSERT INTO t1 VALUES
(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
INSERT INTO t2 VALUES
(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
then Mariadb 5.5 will return the following explain output for the query
SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a
MariaDB [test]> EXPLAIN
-> SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------+
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 4 | Using index |
| 1 | SIMPLE | t2 | ref | i_a | i_a | 5 | test.t1.a | 2 | Using index |
------------------------------------------------------------------------------+
The expected number of the examined rows from t1 is 4 though it should be 8 as for the following query:
MariaDB [test]> EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000;
------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------+
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 8 | Using index |
| 1 | SIMPLE | t2 | ref | i_a | i_a | 5 | test.t1.a | 2 | Using index |
------------------------------------------------------------------------------+
Attachments
Activity
Re: The number of expected rows to be examined is off for a query with ORDER BY
See also bug #13528826 from mysql-trunk.
| Field | Original Value | New Value |
|---|---|---|
| Labels | Launchpad |
| Fix Version/s | Maria 5.5 [ 11303 ] | |
| Labels | Launchpad | Launchpad MariaDB_5.5 |
| Fix Version/s | 5.5.29 [ 11701 ] |
| Labels | Launchpad MariaDB_5.5 | Launchpad |
| Description |
If to create and populate tables t1,t2 with the commands CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam; INSERT INTO t1 VALUES (5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60); CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam; INSERT INTO t2 VALUES (103, 7), (109, 3), (102, 3), (108, 1), (106, 3), (107, 7), (105, 1), (101, 3), (100, 7), (110, 1); then Mariadb 5.5 will return the following explain output for the query SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a MariaDB [test]> EXPLAIN -> SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a; +------+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ | 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 4 | Using index | | 1 | SIMPLE | t2 | ref | i_a | i_a | 5 | test.t1.a | 2 | Using index | +------+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ The expected number of the examined rows from t1 is 4 though it should be 8 as for the following query: MariaDB [test]> EXPLAIN -> SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a; +------+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ | 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 4 | Using index | | 1 | SIMPLE | t2 | ref | i_a | i_a | 5 | test.t1.a | 2 | Using index | +------+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ |
If to create and populate tables t1,t2 with the commands CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam; INSERT INTO t1 VALUES (5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60); CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam; INSERT INTO t2 VALUES (103, 7), (109, 3), (102, 3), (108, 1), (106, 3), (107, 7), (105, 1), (101, 3), (100, 7), (110, 1); then Mariadb 5.5 will return the following explain output for the query SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a MariaDB [test]> EXPLAIN -> SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a; +------+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ | 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 4 | Using index | | 1 | SIMPLE | t2 | ref | i_a | i_a | 5 | test.t1.a | 2 | Using index | +------+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ The expected number of the examined rows from t1 is 4 though it should be 8 as for the following query: MariaDB [test]> EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 1000; +------+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ | 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 8 | Using index | | 1 | SIMPLE | t2 | ref | i_a | i_a | 5 | test.t1.a | 2 | Using index | +------+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+ |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | defaullt [ 21466 ] | MariaDB v2 [ 45934 ] |
| Workflow | MariaDB v2 [ 45934 ] | MariaDB v3 [ 66624 ] |
| Workflow | MariaDB v3 [ 66624 ] | MariaDB v4 [ 145036 ] |
Launchpad bug id: 1002508