MySQL Bugs: #26284: bug #26273 / optimize, not executed for index, if datafile is gap-free

Bug #26284	bug #26273 / optimize, not executed for index, if datafile is gap-free
Submitted:	12 Feb 2007 12:32	Modified:	11 Nov 2007 21:35
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.bk	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	bfsm_2007_03_01, Optimize, qc, regression

Description:
reopen of bug #26273

Couldn't re-open the bug itself, once it is on cannot repeat.

Indeed my example did not show the problem (It would with the fix to bug# 20944)

I have changed the sql for the explain, to show the difference.
(tested with todays 5.0.bk, and the 4.1.22).

On 5.0.bk myisam and innodb return the following results:

The first explain, optimized after only insert: 423 rows
The 2nd explain (after insert/delete) 512 rows

Additional, you should be able to note the difference in the time required by the optimize. (the time diff is probably only noticeable  on a debug build, with debug enabled)

explain select * from i1 where a in (11,12,16,17,21,22,26,27) and b in (8,12,16,24,32,48,64,96);

(If both show 512 rows, please remove the last 96 from the IN)

The configure used (so I do not thing it will make a diffference:
./configure --with-debug --with-federated-storage-engine --with-blackhole-storage-engine --with-csv-storage-engine --with-archive-storage-engine --with-extra-charset=all

Server started as (from mysql-test directory)
 ../sql/mysqld --no-defaults --port=4002 --basedir=. --datadir=./var/master-data --skip-innodb --skip-ndbcluster --skip-bdb --language=../sql/share/english/ --character-sets-dir=../sql/share/charsets/ --socket var/tmp/msock --log-error=errlog --log=log --debug &

I think there should be no other setting anywhere else?

How to repeat:
drop table if exists i1;
create table i1 (a int not null, b int not null, index (a,b));
insert into i1 values (1,2), (1,3), (2,3), (2,4);
insert into i1 select a+5, b*2 from i1;
insert into i1 select a+5, b*2 from i1;
insert into i1 select a+5, b*2 from i1;
insert into i1 select a+5, b*2 from i1;
insert into i1 select a+5, b*2 from i1;
insert into i1 select a+5, b*2 from i1;
insert into i1 select a+5, b*2 from i1;
analyze table i1;

optimize table i1;
# 512 rows
explain select * from i1 where a in (11,12,16,17,21,22,26,27) and b in (8,12,16,24,32,48,64,96);

insert into i1 select 555,555; delete from i1 where a = 555;
optimize table i1;

# 423 rows
explain select * from i1 where a in (11,12,16,17,21,22,26,27) and b in (8,12,16,24,32,48,64,96);

Suggested fix:
-

Swapped the numbers. the first explain oes 512, the 2nd 423

Sorry, it only happens for myisam. Innodb works fine
(I didnt realize, that I had disabled innadb...)

Please test with myisam.

Thank you for a bug report. Verified just as described with MyISAM table and latest 5.0.36-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.36 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists i1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table i1 (a int not null, b int not null, index (a,b));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into i1 values (1,2), (1,3), (2,3), (2,4);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into i1 select a+5, b*2 from i1;
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into i1 select a+5, b*2 from i1;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> insert into i1 select a+5, b*2 from i1;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> insert into i1 select a+5, b*2 from i1;
Query OK, 32 rows affected (0.00 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> insert into i1 select a+5, b*2 from i1;
iQuery OK, 64 rows affected (0.00 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> insert into i1 select a+5, b*2 from i1;
iQuery OK, 128 rows affected (0.01 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql> insert into i1 select a+5, b*2 from i1;
Query OK, 256 rows affected (0.01 sec)
Records: 256  Duplicates: 0  Warnings: 0

amysql> analyze table i1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.i1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> optimize table i1;
+---------+----------+----------+----------+
| Table   | Op       | Msg_type | Msg_text |
+---------+----------+----------+----------+
| test.i1 | optimize | status   | OK       |
+---------+----------+----------+----------+
1 row in set (0.01 sec)

mysql> explain select * from i1 where a in (11,12,16,17,21,22,26,27) and b in
    -> (8,12,16,24,32,48,64,96)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i1
         type: index
possible_keys: a
          key: a
      key_len: 8
          ref: NULL
         rows: 512
        Extra: Using where; Using index
1 row in set (0.02 sec)

mysql> select count(*) from i1;
+----------+
| count(*) |
+----------+
|      512 |
+----------+
1 row in set (0.00 sec)

mysql> insert into i1 select 555,555; delete from i1 where a = 555;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.01 sec)

mysql> select count(*) from i1;
+----------+
| count(*) |
+----------+
|      512 |
+----------+
1 row in set (0.00 sec)

mysql> optimize table i1;
+---------+----------+----------+----------+
| Table   | Op       | Msg_type | Msg_text |
+---------+----------+----------+----------+
| test.i1 | optimize | status   | OK       |
+---------+----------+----------+----------+
1 row in set (0.02 sec)

mysql> explain select * from i1 where a in (11,12,16,17,21,22,26,27) and b in (
8,12,16,24,32,48,64,96)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i1
         type: range
possible_keys: a
          key: a
      key_len: 8
          ref: NULL
         rows: 423
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> select count(*) from i1 where a in (11,12,16,17,21,22,26,27) and b in (8
,12,16,24,32,48,64,96)\G
*************************** 1. row ***************************
count(*): 427
1 row in set (0.00 sec)

This bug report indicates a probably serious flaw in the procedure that estimates the number of rows in a sequence of ranges or even in the procedure that builds this sequence of ranges.

However it does not provide any queries with wrong result sets.

True this bug is not affecting the result set of any data select.
However it is in contradiction to the documentation at:
http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html 
>>
 For MyISAM tables, OPTIMIZE TABLE works as follows:
   1. If the table has deleted or split rows, repair the table.
   2. If the index pages are not sorted, sort them.
   3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.
<<

There is no indication that point 2 (and 3) are not executed, if point 1 is skipped.

As for test-queries.
The result of the explain in the original report is affected. ( so the result set of the explain (after optimize) is wrong.

It is acceptable that the explain does not return a better expectation, before attempting the optimize. (After all that is part of what optimize is there for)

BTW, compare the result of the explain before optimize between 4.1 and 5.0
=> 4.1 maintains a far better index distribution during/after the insert only, than 5.0 does. (This is a regression)

This problem will be fixed later, probably in 5.2