[MDEV-18094] Query with order by limit picking index scan over filesort - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.2.28, 10.1.42, 10.3.19, 10.4.9
Component/s: Optimizer
Labels:
- order-by-optimization

Description

The dataset used

create table t0 (a int);

INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0);

CREATE TABLE t1 (

a int(11) DEFAULT NULL,

b int(11) DEFAULT NULL,

d int(11) DEFAULT NULL,

KEY b_3 (a,d),

KEY b_4 (a,b)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C, t0 D;

MariaDB [test]> select count(*) from t1;

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

| count(*) |

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

|    10000 |

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

Now running the query without any limit

MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b;

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

| id   | select_type | table | type | possible_keys | key  | key_len | ref         | rows | r_rows | filtered | r_filtered | Extra                       |

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

|    1 | SIMPLE      | t1    | ref  | b_3,b_4       | b_3  | 10      | const,const |  200 | 200.00 |   100.00 |     100.00 | Using where; Using filesort |

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

Now running the query with limit= NUMBER OF TABLE RECORDS

MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 10000;

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

| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | r_rows  | filtered | r_filtered | Extra       |

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

|    1 | SIMPLE      | t1    | range | b_3,b_4       | b_4  | 5       | NULL | 2000 | 2000.00 |    10.00 |      10.00 | Using where |

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

This looks incorrect, we definitely have a problem with the cost model that changes ref access -> index(or range) by which we can do the ORDERING.

With limit = 2x table_records

MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 20000;

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

| id   | select_type | table | type  | possible_keys | key  | key_len | ref         | rows  | r_rows   | filtered | r_filtered | Extra       |

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

|    1 | SIMPLE      | t1    | index | b_3,b_4       | b_4  | 10      | const,const | 10266 | 10000.00 |     1.95 |       2.00 | Using where |

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

Attachments

Issue Links

relates to

MDEV-8306 Complete cost-based optimization for ORDER BY with LIMIT

Stalled

MDEV-17761 Odd optimizer choice with ORDER BY LIMIT and condition selectivity

Stalled

Activity

Ascending order - Click to sort in descending order

Varun Gupta (Inactive) created issue - 2018-12-27 19:09

Varun Gupta (Inactive) made changes - 2018-12-27 19:14

Field	Original Value	New Value
Description		The dataset used {code:sql} create table t0 (a int); INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0); CREATE TABLE t1 ( a int(11) DEFAULT NULL, b int(11) DEFAULT NULL, d int(11) DEFAULT NULL, KEY b_3 (a,d), KEY b_4 (a,b) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C, t0 D; {code} MariaDB [test]> select count() from t1; +----------+ \| count() \| +----------+ \| 10000 \| +----------+ Now running the query without any limit MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b; +------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+ \| 1 \| SIMPLE \| t1 \| ref \| b_3,b_4 \| b_3 \| 10 \| const,const \| 200 \| 200.00 \| 100.00 \| 100.00 \| Using where; Using filesort \| +------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+ Now running the query with limit= NUMBER OF TABLE RECORDS MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 10000; +------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| r_rows \| filtered \| r_filtered \| Extra \| +------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+ \| 1 \| SIMPLE \| t1 \| range \| b_3,b_4 \| b_4 \| 5 \| NULL \| 2000 \| 2000.00 \| 10.00 \| 10.00 \| Using where \| +------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+ This looks incorrect, we definitely have a problem with the cost model that changes ref access -> index(or range) by which we can do the ORDERING.

Varun Gupta (Inactive) made changes - 2018-12-27 19:15

Description

The dataset used

{code:sql}
create table t0 (a int);
INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0);

CREATE TABLE t1 (
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
d int(11) DEFAULT NULL,
KEY b_3 (a,d),
KEY b_4 (a,b)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C, t0 D;
{code}

MariaDB [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+

Now running the query without any limit
MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b;
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
| 1 | SIMPLE | t1 | ref | b_3,b_4 | b_3 | 10 | const,const | 200 | 200.00 | 100.00 | 100.00 | Using where; Using filesort |
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+

Now running the query with limit= NUMBER OF TABLE RECORDS
MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 10000;
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
| 1 | SIMPLE | t1 | range | b_3,b_4 | b_4 | 5 | NULL | 2000 | 2000.00 | 10.00 | 10.00 | Using where |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+

This looks incorrect, we definitely have a problem with the cost model that changes ref access -> index(or range) by which we can do the ORDERING.

The dataset used

{code:sql}
create table t0 (a int);
INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0);

CREATE TABLE t1 (
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
d int(11) DEFAULT NULL,
KEY b_3 (a,d),
KEY b_4 (a,b)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C, t0 D;
{code}

{noformat}
MariaDB [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
{noformat}

Now running the query without any limit

{noformat}
MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b;
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
| 1 | SIMPLE | t1 | ref | b_3,b_4 | b_3 | 10 | const,const | 200 | 200.00 | 100.00 | 100.00 | Using where; Using filesort |
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
{noformat}

Now running the query with limit= NUMBER OF TABLE RECORDS

{noformat}
MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 10000;
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
| 1 | SIMPLE | t1 | range | b_3,b_4 | b_4 | 5 | NULL | 2000 | 2000.00 | 10.00 | 10.00 | Using where |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
{noformat}

This looks incorrect, we definitely have a problem with the cost model that changes ref access -> index(or range) by which we can do the ORDERING.

Varun Gupta (Inactive) made changes - 2018-12-27 19:16

Description

The dataset used

{code:sql}
create table t0 (a int);
INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0);

CREATE TABLE t1 (
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
d int(11) DEFAULT NULL,
KEY b_3 (a,d),
KEY b_4 (a,b)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C, t0 D;
{code}

{noformat}
MariaDB [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
{noformat}

Now running the query without any limit

{noformat}
MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b;
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
| 1 | SIMPLE | t1 | ref | b_3,b_4 | b_3 | 10 | const,const | 200 | 200.00 | 100.00 | 100.00 | Using where; Using filesort |
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
{noformat}

Now running the query with limit= NUMBER OF TABLE RECORDS

{noformat}
MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 10000;
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
| 1 | SIMPLE | t1 | range | b_3,b_4 | b_4 | 5 | NULL | 2000 | 2000.00 | 10.00 | 10.00 | Using where |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
{noformat}

This looks incorrect, we definitely have a problem with the cost model that changes ref access -> index(or range) by which we can do the ORDERING.

The dataset used

{code:sql}
create table t0 (a int);
INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0);

CREATE TABLE t1 (
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
d int(11) DEFAULT NULL,
KEY b_3 (a,d),
KEY b_4 (a,b)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C, t0 D;
{code}

{noformat}
MariaDB [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
{noformat}

Now running the query without any limit

{noformat}
MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b;
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
| 1 | SIMPLE | t1 | ref | b_3,b_4 | b_3 | 10 | const,const | 200 | 200.00 | 100.00 | 100.00 | Using where; Using filesort |
+------+-------------+-------+------+---------------+------+---------+-------------+------+--------+----------+------------+-----------------------------+
{noformat}

Now running the query with limit= NUMBER OF TABLE RECORDS

{noformat}
MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 10000;
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
| 1 | SIMPLE | t1 | range | b_3,b_4 | b_4 | 5 | NULL | 2000 | 2000.00 | 10.00 | 10.00 | Using where |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------+----------+------------+-------------+
{noformat}

This looks incorrect, we definitely have a problem with the cost model that changes ref access -> index(or range) by which we can do the ORDERING.

With limit = 2x table_records

{noformat}
MariaDB [test]> analyze select a,b,d from t1 where a=1 and d=2 order by b limit 20000;
+------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+
| 1 | SIMPLE | t1 | index | b_3,b_4 | b_4 | 10 | const,const | 10266 | 10000.00 | 1.95 | 2.00 | Using where |
+------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+
{noformat}

Varun Gupta (Inactive) added a comment - 2018-12-27 19:26

Some numbers from the optimizer trace

            "reconsidering_access_paths_for_index_ordering": {

              "clause": "ORDER BY",

              "fanout": 1,

              "read_time": 82.001,

              "table_name": "t1",

              "rows_estimation": 200,

              "possible_keys": [

                  "index": "b_3",

                  "can_resolve_order": false,

                  "cause": "not_usable_index_for_the query"

},

                  "index": "b_4",

                  "can_resolve_order": true,

                  "updated_limit": 10266,

                  "range_scan_time": 82,

                  "index_scan_time": 82,

                  "records": 2000,

                  "chosen": true

Varun Gupta (Inactive) added a comment - 2018-12-27 19:26 Some numbers from the optimizer trace { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "fanout": 1, "read_time": 82.001, "table_name": "t1", "rows_estimation": 200, "possible_keys": [ { "index": "b_3", "can_resolve_order": false, "cause": "not_usable_index_for_the query" }, { "index": "b_4", "can_resolve_order": true, "updated_limit": 10266, "range_scan_time": 82, "index_scan_time": 82, "records": 2000, "chosen": true } ] } }

Varun Gupta (Inactive) made changes - 2018-12-27 19:29

Labels

order-by-optimization

Varun Gupta (Inactive) made changes - 2019-01-14 18:36

Link

This issue relates to MDEV-17761 [ MDEV-17761 ]

Varun Gupta (Inactive) added a comment - 2019-02-14 14:07 - edited

With histograms

MariaDB [test]> set histogram_size=255;

Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> analyze table t2 persistent for all;

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

| Table   | Op      | Msg_type | Msg_text                                |

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

| test.t2 | analyze | status   | Engine-independent statistics collected |

| test.t2 | analyze | status   | OK                                      |

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

2 rows in set (1.267 sec)

MariaDB [test]> analyze select a,b,c from t2 where a=1 and c=2 order by b limit 10000;

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

| id   | select_type | table | type  | possible_keys | key  | key_len | ref         | rows  | r_rows   | filtered | r_filtered | Extra       |

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

|    1 | SIMPLE      | t2    | index | a_c,a_b       | a_b  | 10      | const,const | 10000 | 10000.00 |     2.00 |       2.00 | Using where |

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

1 row in set (0.761 sec)

WITHOUT EITS

MariaDB [test]> set use_stat_tables= NEVER;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> analyze select a,b,c from t2 where a=1 and c=2 order by b limit 10000;

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

| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | r_rows  | filtered | r_filtered | Extra       |

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

|    1 | SIMPLE      | t2    | range | a_c,a_b       | a_b  | 5       | NULL | 2000 | 2000.00 |    10.00 |      10.00 | Using where |

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

1 row in set (0.163 sec)

So even with histograms we are picking up index_scan over range_scan!

Sergei Petrunia added a comment - 2019-02-14 16:26

MariaDB [test]> analyze select a,b,c from t2 where a=1 and c=2 order by b limit 10000;

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

| id   | select_type | table | type  | possible_keys | key  | key_len | ref         | rows  | r_rows   | filtered | r_filtered | Extra       |

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

|    1 | SIMPLE      | t2    | index | a_c,a_b       | a_b  | 10      | const,const | 10000 | 10000.00 |     2.00 |       2.00 | Using where |

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

1 row in set (0.761 sec)

Looks like an invalid plan:

why would we use a full index scan (type=index) on index a_b when a ref(or range) access is possible, over a=1 ?
if type=index, why "ref" is non-empty? and how come it is "const,const" - we dont have anything for the second component?

Sergei Petrunia added a comment - 2019-02-14 16:26 MariaDB [test]> analyze select a,b,c from t2 where a=1 and c=2 order by b limit 10000; +------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+ | 1 | SIMPLE | t2 | index | a_c,a_b | a_b | 10 | const,const | 10000 | 10000.00 | 2.00 | 2.00 | Using where | +------+-------------+-------+-------+---------------+------+---------+-------------+-------+----------+----------+------------+-------------+ 1 row in set (0.761 sec) Looks like an invalid plan: why would we use a full index scan (type=index) on index a_b when a ref(or range) access is possible, over a=1 ? if type=index, why "ref" is non-empty? and how come it is "const,const" - we dont have anything for the second component?

Varun Gupta (Inactive) made changes - 2019-03-14 13:30

Status

Open [ 1 ]

In Progress [ 3 ]

Valerii Kravchuk made changes - 2019-03-26 14:55

Support case ID

27078

Varun Gupta (Inactive) added a comment - 2019-08-26 15:24

Patch
http://lists.askmonty.org/pipermail/commits/2019-August/013946.html

Varun Gupta (Inactive) added a comment - 2019-08-26 15:24 Patch http://lists.askmonty.org/pipermail/commits/2019-August/013946.html

Varun Gupta (Inactive) made changes - 2019-08-27 16:22

Assignee	Varun Gupta [ varun ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Varun Gupta (Inactive) made changes - 2019-09-10 09:36

Link

This issue relates to MDEV-8306 [ MDEV-8306 ]

Sergei Petrunia added a comment - 2019-09-19 11:06

Ok to push.

Sergei Petrunia added a comment - 2019-09-19 11:06 Ok to push.

Sergei Petrunia made changes - 2019-09-19 11:06

Assignee	Sergei Petrunia [ psergey ]	Varun Gupta [ varun ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Petrunia added a comment - 2019-09-19 11:07

The patch fixes some of the cases. But does it fix all cases listed in the MDEV? (AFIAU, no?)

Sergei Petrunia added a comment - 2019-09-19 11:07 The patch fixes some of the cases. But does it fix all cases listed in the MDEV? (AFIAU, no?)

Varun Gupta (Inactive) made changes - 2019-09-21 06:45

Fix Version/s		10.1.42 [ 23407 ]
Fix Version/s		10.2.28 [ 23910 ]
Fix Version/s		10.3.19 [ 23908 ]
Fix Version/s		10.4.9 [ 23906 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.1 [ 16100 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Varun Gupta (Inactive) added a comment - 2019-09-21 06:48

In this issue we see for limit = 10000 plan was changed from ref+Filesort to range scan.
but with limit = 20000, the number of records in the table is 10000, we picked index scan which is not at all beneficial.
So this patch fixes that even with limit > table records, if we planned to use range scan we should force creating a QUICK_SELECT.

Varun Gupta (Inactive) added a comment - 2019-09-21 06:48 In this issue we see for limit = 10000 plan was changed from ref+Filesort to range scan. but with limit = 20000, the number of records in the table is 10000, we picked index scan which is not at all beneficial. So this patch fixes that even with limit > table records, if we planned to use range scan we should force creating a QUICK_SELECT.

Sergei Golubchik made changes - 2021-12-06 21:48

Workflow

MariaDB v3 [ 91420 ]

MariaDB v4 [ 155415 ]

People

Assignee:: Varun Gupta (Inactive)

Reporter:: Varun Gupta (Inactive)

Votes:: 1 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2018-12-27 19:09

Updated:: 2019-09-24 11:35

Resolved:: 2019-09-21 06:45

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

With histograms

WITHOUT EITS

People

Dates

Git Integration