Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.17
-
None
-
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 |
|
+-----------------+ |