Details
-
Type:
Bug
-
Status: Closed
-
Resolution: Not a Bug
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
The following view:
CREATE VIEW v1 AS SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9 ;
is executed with an inferior plan after mwl#106 .
Before mwl106:
explain:
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9
2 DERIVED t1 range b,b_2 b 4 NULL 226 Using where; Using temporary
counters:
Handler_read_next 0
Handler_read_prev 8
Handler_read_rnd 0
Handler_read_rnd_next 19
Handler_write 20
after mwl106:
explain:
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 222
2 DERIVED t1 ref b,b_2 b_2 4 const 222 Using where; Using index; Using temporary; Using filesort
counters:
-Handler_read_next 208
-Handler_read_prev 0
-Handler_read_rnd 9
-Handler_read_rnd_next 219
-Handler_tmp_update 0
-Handler_tmp_write 217
-Handler_write 2
The results returned are also different, but I do not know if the difference is legitimate or not.
Test case:
CREATE TABLE t1 (
a INT,
b INT NOT NULL,
c char(100),
KEY (b, c),
KEY (b, a, c)
)
INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5), (6, 6, 6), (7, 7, 7), (8, 8, 8), (9, 9, 9);
INSERT INTO t1 SELECT a + 10, b, c FROM t1;
INSERT INTO t1 SELECT a + 20, b, c FROM t1;
INSERT INTO t1 SELECT a + 40, b, c FROM t1;
INSERT INTO t1 SELECT a + 80, b, c FROM t1;
INSERT INTO t1 SELECT a + 160, b, c FROM t1;
INSERT INTO t1 SELECT a + 320, b, c FROM t1;
INSERT INTO t1 SELECT a + 640, b, c FROM t1;
INSERT INTO t1 SELECT a + 1280, b, c FROM t1 LIMIT 80;
CREATE VIEW v1 AS SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9 ;
EXPLAIN SELECT * FROM v1;
FLUSH STATUS;
SELECT * FROM v1;
SHOW STATUS LIKE '%Handler%';
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Inferior plan selected after mwl#106 with ORDER BY
This bug is absolutely unrelated to views.
I've got similar results without views:
MariaDB [test]> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9 ;
------
| a |
------
| 41 |
| 201 |
| 361 |
| 521 |
| 681 |
| 841 |
| 1001 |
| 1161 |
| 1321 |
------
9 rows in set (0.00 sec)
MariaDB [test]> SHOW STATUS LIKE '%Handler%';
---------------------------------+
| Variable_name | Value |
---------------------------------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 208 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 9 |
| Handler_read_rnd_next | 209 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_tmp_update | 0 |
| Handler_tmp_write | 208 |
| Handler_update | 0 |
| Handler_write | 0 |
---------------------------------+
17 rows in set (0.01 sec)
MariaDB [test]> EXPLAIN SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9 ;
------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | t1 | ref | b,b_2 | b_2 | 4 | const | 214 | Using where; Using index; Using temporary; Using filesort |
------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Re: Inferior plan selected after mwl#106 with ORDER BY
Philip,
Why do you think the plan that uses a covering index is less efficient that the plan using a non-covering index?
Re: Inferior plan selected after mwl#106 with ORDER BY
I reproduced the situation with more rows by creating and populating the table as described above and then using INSERT ... SELECT to bring the total row count to 630784. In this case, the latest revision executes SELECT * FROM v1 in about 0.15 sec, whereas the revision before WL#106 takes 0.10 seconds on average. So it seems that the plan that looked to me inferior due to counters is also inferior in practice.
The plan and the performance for the query
SELECT DISTINCT a FROM t1 WHERE b = 1 ORDER BY c DESC LIMIT 0, 9 ;
is identical for both revisions. However the plan and the performance for SELECT * FROM v1; are different across revisions.
In addition, I think it is important to figure out why the UDF in the other example gets called twice per row.
Launchpad bug id: 802858
Re: Inferior plan selected after mwl#106 with ORDER BY
Here is another instance of the same problem. In the test case below, mwl106 causes the UDF to be executed twice per row. The counters report the use of Handler_tmp_write :
--source include/have_udf.inc
eval CREATE FUNCTION sequence RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (0),(0),(0),(0);
CREATE VIEW v1 AS SELECT sequence() AS seq FROM t1 ORDER BY seq ASC ;
FLUSH STATUS;
SELECT * FROM v1;
SHOW STATUS LIKE '%Handler_%';