[MDEV-5598] Index range scan ignored if first condition is impossible - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Cannot Reproduce
Affects Version/s: 10.0.7
Fix Version/s: None
Component/s: None
Labels:
None

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)

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

MDEV-5598.log
7 kB
2014-02-05 16:00
MDEV-5598.sql
0.4 kB
2014-02-05 16:00

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2014-01-31 17:30

Axel,

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

Show

Elena Stepanova added a comment - 2014-01-31 17:30 Axel, Could you please check if there is a known issue from sysbench test results related to this bug report?

Hide

Permalink

Axel Schwenke added a comment - 2014-02-05 16:00

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.

Show

Axel Schwenke added a comment - 2014-02-05 16:00 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.

Hide

Permalink

Axel Schwenke added a comment - 2014-02-05 16:23

Stephane, we need more information to reproduce this.

Show

Axel Schwenke added a comment - 2014-02-05 16:23 Stephane, we need more information to reproduce this.

Hide

Permalink

VAROQUI Stephane added a comment - 2014-02-05 23:20 - edited

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 :

Show

VAROQUI Stephane added a comment - 2014-02-05 23:20 - edited 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 :

Hide

Permalink

VAROQUI Stephane added a comment - 2014-02-05 23:20 - edited

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:

Show

VAROQUI Stephane added a comment - 2014-02-05 23:20 - edited 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:

Hide

Permalink

VAROQUI Stephane added a comment - 2014-02-05 23:20 - edited

 
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 :

Show

Hide

Permalink

Sergei Petrunia added a comment - 2014-02-19 16:20

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?

Show

Sergei Petrunia added a comment - 2014-02-19 16:20 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?

Hide

Permalink

Axel Schwenke added a comment - 2014-02-19 16:20

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?

Show

Axel Schwenke added a comment - 2014-02-19 16:20 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?

Hide

Permalink

Axel Schwenke added a comment - 2014-02-27 21:19

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

Show

Axel Schwenke added a comment - 2014-02-27 21:19 please reopen this issue if there is new information how to reproduce

People

Assignee:

Axel Schwenke

Reporter:

VAROQUI Stephane

Votes:

0 Vote for this issue

Watchers:

5 Start watching this issue

Dates

Due:

2014-03-03

Created:

2014-01-31 16:02

Updated:

2014-02-27 21:19

Resolved:

2014-02-27 21:19

Time Tracking

Estimated:

Not Specified

Remaining:

Logged:

Agile

View on Board