[MDEV-2998] LP:802858 - Inferior plan selected after mwl#106 with ORDER BY Created: 2011-06-28  Updated: 2015-02-02  Resolved: 2012-10-04

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

Type: Bug
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Not a Bug Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug802858.xml    

 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%';

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-06-28 ]

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_%';

Comment by Igor Babaev [ 2011-07-11 ]

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)

Comment by Igor Babaev [ 2011-07-11 ]

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?

Comment by Philip Stoev (Inactive) [ 2011-07-11 ]

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.

Comment by Rasmus Johansson (Inactive) [ 2011-08-11 ]

Launchpad bug id: 802858

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