[MDEV-11577] Mariadb Not using PRIMARY KEY with IN condition -- Very strange Created: 2016-12-15  Updated: 2016-12-19

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.17
Fix Version/s: 10.1

Type: Bug Priority: Major
Reporter: suraj chauhan Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

CentOS Linux release 7.0.1406 (Core)


 Description   

While looking explain plan one of our testing query, We observe that Mariadb 10.1.17 is not using PRIMARY KEY with IN condition while same execution plan is working with MySQL 5.6 & 5.7.

Strange behavior noted here.

Here is the details:

Table structure:

MariaDB [part]> show create table chk\G
 
*************************** 1. row ***************************
 
       Table: chk
 
Create Table: CREATE TABLE `chk` (
 
  `id` bigint(20) NOT NULL,
 
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
1 row in set (0.04 sec)

mariadb server details:

MariaDB [part]> \s;
 
--------------
 
/opt/mariadb2/bin/mysql  Ver 15.1 Distrib 10.1.17-MariaDB, for Linux (x86_64) using readline 5.1
 
 
 
Connection id:          18321066
 
Current database:       part
 
Current user:           admin@localhost
 
SSL:                    Not in use
 
Current pager:          stdout
 
Using outfile:          ''
 
Using delimiter:        ;
 
Server:                 MariaDB
 
Server version:         10.1.17-MariaDB MariaDB Server
 
Protocol version:       10
 
Connection:             Localhost via UNIX socket
 
Server characterset:    latin1
 
Db     characterset:    latin1
 
Client characterset:    utf8
 
Conn.  characterset:    utf8
 
UNIX socket:            /data/mariadb-prod/mariadb-data2/mariadb.sock
 
Uptime:                 14 days 16 hours 17 min 52 sec
 
 
 
Threads: 29  Questions: 427905592  Slow queries: 19894  Opens: 2960  Flush tables: 1  Open tables: 2795  Queries per second avg: 337.392


MariaDB [part]> select * from chk; 
+------+---------------------+ 
| id | dt | 
+------+---------------------+ 
| 3423 | 2016-12-14 13:13:41 | 
| 3424 | 2016-12-14 13:13:44 | 
| 3425 | 2016-12-14 13:15:07 | 
+------+---------------------+ 
3 rows in set (0.03 sec) 


MariaDB [part]> select * from chk where id in(3423,433,345,56,5476,123,3425);
 
+------+---------------------+
 
| id   | dt                  |
 
+------+---------------------+
 
| 3423 | 2016-12-14 13:13:41 |
 
| 3425 | 2016-12-14 13:15:07 |
 
+------+---------------------+
 
2 rows in set (0.01 sec)


MariaDB [part]> explain extended select * from chk where id in(3423,433,345,56,5476,123,3425);
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
|    1 | SIMPLE      | chk   | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 |   100.00 | Using where |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
1 row in set, 1 warning (0.03 sec)


MariaDB [part]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.1.17-MariaDB |
 
+-----------------+



 Comments   
Comment by Elena Stepanova [ 2016-12-17 ]

Is it a real-life example, or do you have more data in the table?

I think in this particular case there are just too few values to make any difference. If I insert more, the index is used:

MariaDB [test]> select * from chk; 
+------+---------------------+ 
| id | dt | 
+------+---------------------+ 
| 3423 | 2016-12-14 13:13:41 | 
| 3424 | 2016-12-14 13:13:44 | 
| 3425 | 2016-12-14 13:15:07 | 
+------+---------------------+ 
3 rows in set (0.03 sec) 


MariaDB [test]>  explain extended select * from chk where id in(3423,433,345,56,5476,123,3425);
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
|    1 | SIMPLE      | chk   | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 |   100.00 | Using where |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
1 row in set, 1 warning (0.00 sec)


MariaDB [test]> select * from chk;
 
+------+---------------------+
 
| id   | dt                  |
 
+------+---------------------+
 
| 3423 | 2016-12-14 13:13:41 |
 
| 3424 | 2016-12-14 13:13:44 |
 
| 3425 | 2016-12-14 13:15:07 |
 
| 3426 | 2016-12-17 14:44:26 |
 
| 3427 | 2016-12-17 14:44:26 |
 
| 3428 | 2016-12-17 14:44:26 |
 
| 3429 | 2016-12-17 14:44:26 |
 
| 3430 | 2016-12-17 14:44:26 |
 
| 3431 | 2016-12-17 14:44:44 |
 
| 3432 | 2016-12-17 14:44:44 |
 
| 3433 | 2016-12-17 14:44:44 |
 
| 3434 | 2016-12-17 14:44:44 |
 
| 3435 | 2016-12-17 14:44:44 |
 
| 3436 | 2016-12-17 14:44:59 |
 
| 3437 | 2016-12-17 14:44:59 |
 
| 3438 | 2016-12-17 14:44:59 |
 
| 3439 | 2016-12-17 14:44:59 |
 
| 3440 | 2016-12-17 14:44:59 |
 
| 3441 | 2016-12-17 14:45:21 |
 
| 3442 | 2016-12-17 14:45:21 |
 
| 3443 | 2016-12-17 14:45:21 |
 
| 3444 | 2016-12-17 14:45:21 |
 
| 3445 | 2016-12-17 14:45:21 |
 
| 3446 | 2016-12-17 14:45:35 |
 
| 3447 | 2016-12-17 14:45:35 |
 
| 3448 | 2016-12-17 14:45:35 |
 
| 3449 | 2016-12-17 14:45:35 |
 
| 3450 | 2016-12-17 14:45:35 |
 
+------+---------------------+
 
28 rows in set (0.00 sec)


MariaDB [test]>  explain extended select * from chk where id in(3423,433,345,56,5476,123,3425);
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
 
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
 
|    1 | SIMPLE      | chk   | range | PRIMARY       | PRIMARY | 8       | NULL |    7 |   100.00 | Using where |
 
+------+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
 
1 row in set, 1 warning (0.00 sec)

Comment by suraj chauhan [ 2016-12-19 ]

Thanks Elena,
Yes,execution plan was taken on small data.

Comment by suraj chauhan [ 2016-12-19 ]

Primary key is not getting used on of our mariadb production . Here is the execution plan from MySQL & Mariadb:

Table - txn is having 310 GB data

Expected Execution Plan from MySQL:

From Mysql 5.6:-

mysql> explain SELECT * from txn t WHERE `id` IN ('5929327726','5870245919','5924017143','5926260554','5930180208','5930064544','5930449454','5930453368','59316529205931626930','5929610227','5930265658','5930315549','5931232234','5931132474') ORDER BY t.bank_ref_no LIMIT 0,100\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: t
 
         type: range
 
possible_keys: PRIMARY
 
          key: PRIMARY
 
      key_len: 8
 
          ref: NULL
 
         rows: 13
 
        Extra: Using where; Using filesort
 
1 row in set (0.02 sec)

This is strange to all of us:

From Mariadb:-

Database changed
 
mysql> explain SELECT * from txn t WHERE `id` IN ('5929327726','5870245919','5924017143','5926260554','5930180208','5930064544','5930449454','5930453368','59316529205931626930','5929610227','5930265658','5930315549','5931232234','5931132474') ORDER BY t.bank_ref_no LIMIT 0,100\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: t
 
         type: ALL
 
possible_keys: PRIMARY
 
          key: NULL
 
      key_len: NULL
 
          ref: NULL
 
         rows: 330496201

Generated at Thu Feb 08 07:51:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.