[MDEV-5598] Index range scan ignored if first condition is impossible Created: 2014-01-31  Updated: 2014-02-27  Due: 2014-03-03  Resolved: 2014-02-27

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

Type: Bug Priority: Major
Reporter: VAROQUI Stephane Assignee: Axel Schwenke
Resolution: Cannot Reproduce Votes: 0
Labels: None

Attachments: File MDEV-5598.log     File MDEV-5598.sql    

 Description    

 
 CREATE TABLE `sbtest` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `k` int(10) unsigned NOT NULL DEFAULT '0',
 
  `c` char(120) NOT NULL DEFAULT '',
 
  `pad` char(60) NOT NULL DEFAULT '',
 
  PRIMARY KEY (`id`),
 
  KEY `k` (`k`)
 
) ENGINE=InnoDB AUTO_INCREMENT=10000002 DEFAULT CHARSET=latin1;
 
 
 
select `id`,`k` from `bsbackend8`.`sbtest` where  id > 2 and id <1000 ;
 
 
 
| Handler_read_next             | 946268 |
 
 
 
explain select `id`,`k` from `sbtest` where  id > 2 and id <1000 ;
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
| id   | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
|    1 | SIMPLE      | sbtest | index | PRIMARY       | k    | 4       | NULL |    1 | Using where; Using index |
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
 
 
select count(*) from sbtest;
 
+----------+
 
| count(*) |
 
+----------+
 
|   946268 |
 
+----------+

I get some additional info the raison of the full index scan is because the start value of the range does not exists in the table 

 
select min(id) from sbtest;
 
+---------+
 
| min(id) |
 
+---------+
 
|       6 |
 
+---------+
 
1 row in set (0,00 sec)
 
 
 
mysql> explain select `id`,`k` from `sbtest` where  id between 6 and 1000 ;
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
| id   | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
|    1 | SIMPLE      | sbtest | index | PRIMARY       | k    | 4       | NULL |    1 | Using where; Using index |
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
1 row in set (0,00 sec)
 
 
 
mysql> explain select `id`,`k` from `sbtest` where  id between 2 and 1000 ;
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
| id   | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
|    1 | SIMPLE      | sbtest | index | PRIMARY       | k    | 4       | NULL |    1 | Using where; Using index |
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
1 row in set (0,00 sec)
 
 
 
 explain select `id`,`k` from `sbtest` where  id=2 ;
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 
 
 
 explain select `id`,`k` from `sbtest` where  id in (2,6) ;
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
| id   | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
|    1 | SIMPLE      | sbtest | index | PRIMARY       | k    | 4       | NULL |    1 | Using where; Using index |
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
1 row in set (0,00 sec)



 Comments   
Comment by Elena Stepanova [ 2014-01-31 ]

Axel,

Could you please check if there is a known issue from sysbench test results related to this bug report?

Comment by Axel Schwenke [ 2014-02-05 ]

I cannot reproduce this with 10.0.7 (source release, local build). It's also unlikely to see this issue popping up in sysbench, because sysbench never does

SELECT id, k FROM sbtest ...

but all OLTP queries except one select nonindexed column c. The exception is this query:

SELECT SUM(k) FROM sbtest WHERE id BETWEEN ... AND ...

For this query and the one for id,k the scan of the secondary index on (k) is a possible (but unlikely) execution plan. However I haven't hit that once for both types of query and ranges (2,6), (2,1000), (6,1000). It also doesn't matter if extended keys are enabled or not (they are disabled by default). I always end with a range scan on the PRIMARY key.

Comment by Axel Schwenke [ 2014-02-05 ]

Stephane, we need more information to reproduce this.

Comment by VAROQUI Stephane [ 2014-02-05 ]

Hi Axel, 

 
have 2 servers where i copy the same dataset with mysqldump 
 
 
alias backend1='/usr/local/skysql/mysql-client/bin/mysql  --user=skysql --password=skyvodka --host=192.168.0.202 --port=5054'
 
alias backend2='/usr/local/skysql/mysql-client/bin/mysql  --user=skysql --password=skyvodka --host=192.168.0.203 --port=5054'
 
 
 
 
 
backend2 bsbackend5 -e"explain select id,k from sbtest where  id between 6 and 8;"
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
| id   | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
|    1 | SIMPLE      | sbtest | index | PRIMARY       | k    | 4       | NULL |    1 | Using where; Using index |
 
+------+-------------+--------+-------+---------------+------+---------+------+------+--------------------------+
 
 
 
backend1 bsbackend5 -e"explain select id,k from sbtest where  id between 6 and 8;"
 
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
 
| id   | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
 
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
 
|    1 | SIMPLE      | sbtest | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where |
 
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
 
 
 
 
 
backend1 bsbackend5 -e"show create table sbtest"
 
CREATE TABLE `sbtest` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `k` int(10) unsigned NOT NULL DEFAULT '0',
 
  `c` char(120) NOT NULL DEFAULT '',
 
  `pad` char(60) NOT NULL DEFAULT '',
 
  PRIMARY KEY (`id`),
 
  KEY `k` (`k`)
 
) ENGINE=InnoDB AUTO_INCREMENT=10000000 DEFAULT CHARSET=latin1
 
 
 
root@node1:~# backend2 bsbackend5 -e"show create table sbtest"
 
CREATE TABLE `sbtest` (
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  `k` int(10) unsigned NOT NULL DEFAULT '0',
 
  `c` char(120) NOT NULL DEFAULT '',
 
  `pad` char(60) NOT NULL DEFAULT '',
 
  PRIMARY KEY (`id`),
 
  KEY `k` (`k`)
 
) ENGINE=InnoDB AUTO_INCREMENT=10000000 DEFAULT CHARSET=latin1
 
 
 
 
 
52c52
 
< datadir	/var/lib/skysql/db-78b9ef8a/data/
 
---
 
84c84
 
< general_log_file	node2.log
 
---
 
122c122
 
< hostname	node2
 
---
 
208c208
 
< innodb_open_files	4096
 
---
 
270c270
 
< log_error	/var/lib/skysql/db-78b9ef8a/data/node2.err
 
---
 
326c326
 
< open_files_limit	16000
 
---
 
363c363
 
< pid_file	/var/lib/skysql/db-78b9ef8a/data/mysql_sandbox5054.pid
 
---
 
374c374
 
< pseudo_thread_id	1502583
 
---
 
419c419
 
< slave_load_tmpdir	/var/lib/skysql/db-78b9ef8a/tmp
 
---
 
432,433c432,433
 
< slow_query_log_file	node2-slow.log
 
< socket	/tmp/mysql.sock
 
---
 
< table_open_cache	4096
 
---
 
567c470
 
< thread_pool_size	8
 
---
 
573c476
 
< timestamp	1391630983.824567
 
---
 
575c478
 
< tmpdir	/var/lib/skysql/db-78b9ef8a/tmp
 
---
 
 
 
 
 
root@node2:~# ldd --version
 
ldd (Debian EGLIBC 2.17-93) 2.17
 
 
 
 
 
root@node3:~# ldd --version
 
ldd (Debian EGLIBC 2.17-93) 2.17
 
 
 
 
 
 
 
node3 
140128 08:28:43 mysqld_safe Starting mysqld daemon with databases from /var/lib/skysql/db-78b9ef9a/data
 
140128  8:28:43 [Warning] option 'innodb-thread-concurrency': unsigned value 1024 adjusted to 1000
 
140128  8:28:43 [Note] InnoDB: The InnoDB memory heap is disabled
 
140128  8:28:43 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 
140128  8:28:43 [Note] InnoDB: Compressed tables use zlib 1.2.3
 
140128  8:28:43 [Note] InnoDB: CPU does not support crc32 instructions
 
140128  8:28:43 [Note] InnoDB: Using Linux native AIO
 
140128  8:28:43 [Note] InnoDB: Initializing buffer pool, size = 500.0M
 
140128  8:28:43 [Note] InnoDB: Completed initialization of buffer pool
 
140128  8:28:43 [Note] InnoDB: Highest supported file format is Barracuda.
 
140128  8:28:43 [Note] InnoDB: 128 rollback segment(s) are active.
 
140128  8:28:43 [Note] InnoDB: Waiting for purge to start
 
140128  8:28:43 [Note] InnoDB: 5.6.10 started; log sequence number 6914309025
 
140128  8:28:43 [Note] Plugin 'FEEDBACK' is disabled.
 
 
 
 
 
node2
 
140128 08:21:26 mysqld_safe Starting mysqld daemon with databases from /var/lib/skysql/db-78b9ef8a/data
 
140128  8:21:26 [Warning] 'table-open-cache-instances' is MySQL 5.6 compatible option. Not used or needed in MariaDB.
 
140128  8:21:26 [Warning] option 'innodb-thread-concurrency': unsigned value 4096 adjusted to 1000
 
140128  8:21:26 [Note] InnoDB: The InnoDB memory heap is disabled
 
140128  8:21:26 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 
140128  8:21:26 [Note] InnoDB: Compressed tables use zlib 1.2.3
 
140128  8:21:26 [Note] InnoDB: CPU does not support crc32 instructions
 
140128  8:21:26 [Note] InnoDB: Using Linux native AIO
 
140128  8:21:26 [Note] InnoDB: Initializing buffer pool, size = 500.0M
 
140128  8:21:26 [Note] InnoDB: Completed initialization of buffer pool
 
140128  8:21:26 [Note] InnoDB: Highest supported file format is Barracuda.
 
140128  8:21:26 [Note] InnoDB: 128 rollback segment(s) are active.
 
140128  8:21:26 [Note] InnoDB: Waiting for purge to start
 
140128  8:21:26 [Note] InnoDB: 5.6.10 started; log sequence number 14561123264
 
140128  8:21:26 [Note] Plugin 'FEEDBACK' is disabled.
 
 
 
 
 
root@node3:~# md5sum /usr/local/skysql/mariadb/bin/mysqld
 
8f6b5f28a7ff37ad2a34e7534666ff04  /usr/local/skysql/mariadb/bin/mysqld
 
root@node3:~# ldd /usr/local/skysql/mariadb/bin/mysqld
 
	linux-vdso.so.1 (0x00007fffc4cb1000)
 
	libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007ffb42df6000)
 
	libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007ffb42bf4000)
 
	libcrypt.so.1 => /lib/x86_64-linux-gnu/libcrypt.so.1 (0x00007ffb429bc000)
 
	libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007ffb427b8000)
 
	libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007ffb424b1000)
 
	libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007ffb421b2000)
 
	libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007ffb41e06000)
 
	/lib64/ld-linux-x86-64.so.2 (0x00007ffb43025000)
 
	libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007ffb41bf0000)
 
 
 
md5sum /usr/local/skysql/mariadb/bin/mysqld
 
8f6b5f28a7ff37ad2a34e7534666ff04  /usr/local/skysql/mariadb/bin/mysqld
 
root@node2:~# ldd  /usr/local/skysql/mariadb/bin/mysqld
 
	linux-vdso.so.1 (0x00007ffffe1e1000)
 
	libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f5089bdf000)
 
	libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007f50899dd000)
 
	libcrypt.so.1 => /lib/x86_64-linux-gnu/libcrypt.so.1 (0x00007f50897a5000)
 
	libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f50895a1000)
 
	libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f508929a000)
 
	libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f5088f9b000)
 
	libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f5088bef000)
 
	/lib64/ld-linux-x86-64.so.2 (0x00007f5089e11000)
 
	libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f50889d9000)
 
 
 
 
 
Restarted mysqld on node3 does not make any difference

Le Feb 5, 2014 à 3:23 PM, Axel Schwenke (JIRA) a écrit :

Comment by VAROQUI Stephane [ 2014-02-05 ]

Found a difference : 

 
 backend2 bsbackend5 -e"show table status like 'sbtest'\G"
 
*************************** 1. row ***************************
 
           Name: sbtest
 
         Engine: InnoDB
 
        Version: 10
 
     Row_format: Compact
 
           Rows: 0
 
 Avg_row_length: 0
 
    Data_length: 16384
 
Max_data_length: 0
 
   Index_length: 16384
 
      Data_free: 5242880
 
 Auto_increment: 10000000
 
    Create_time: 2014-01-06 14:17:17
 
    Update_time: NULL
 
     Check_time: NULL
 
      Collation: latin1_swedish_ci
 
       Checksum: NULL
 
 Create_options: 
        Comment: 
root@node1:~# backend1 bsbackend5 -e"show table status like 'sbtest'\G"
 
*************************** 1. row ***************************
 
           Name: sbtest
 
         Engine: InnoDB
 
        Version: 10
 
     Row_format: Compact
 
           Rows: 1112372
 
 Avg_row_length: 227
 
    Data_length: 253476864
 
Max_data_length: 0
 
   Index_length: 16302080
 
      Data_free: 5242880
 
 Auto_increment: 10000000
 
    Create_time: 2014-02-05 21:19:38
 
    Update_time: NULL
 
     Check_time: NULL
 
      Collation: latin1_swedish_ci
 
       Checksum: NULL
 
 Create_options: 
        Comment:

Comment by VAROQUI Stephane [ 2014-02-05 ] 

 
analyze table sbtest;
 
+-------------------+---------+----------+----------+
 
| Table             | Op      | Msg_type | Msg_text |
 
+-------------------+---------+----------+----------+
 
| bsbackend5.sbtest | analyze | status   | OK       |
 
+-------------------+---------+----------+----------+
 
1 row in set (0,08 sec)
 
 
 
mysql> explain select `id`,`k` from `sbtest` where  id between 6 and 8 ;
 
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
 
| id   | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
 
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
 
|    1 | SIMPLE      | sbtest | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where |
 
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
 
1 row in set (0,00 sec)

Now after analyze it fixed the plan.

0 row reported in the show table status how can this happen ?

Le Feb 5, 2014 à 3:23 PM, Axel Schwenke (JIRA) a écrit :

Comment by Sergei Petrunia [ 2014-02-19 ]

Hi Stephane,

It seems neither me nor Axel can come up with any idea how did you manage to get the statistics which shows that table has 0 rows while it doesn't have 0 rows. Do you remember if you used any specific innodb settings?

Comment by Axel Schwenke [ 2014-02-19 ]

Hi Stephane,

yes it seems the wrong plan was chosen due to bad table statistics. For InnoDB tables the default is to use persistent statistics which are stored in mysql.innodb_table_stats and mysql.innodb_index_stats. One possibility to get wrong table stats would be wrong data in those tables. Have you set any options in my.cnf that affect how InnoDB collects/updates those numbers? I.e. there is --innodb-stats-auto-recalc. Or have you done something unusual like copying those tables around, restore from dump etc?

Comment by Axel Schwenke [ 2014-02-27 ]

please reopen this issue if there is new information how to reproduce

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