Re: MIN on an indexed field too slow

[Camelia Ticau]

Thans again for looking into my problem.

1) The MySQL version is 4.0.16.
The table is innodb and the index is the primary key (shown in the first thread), which means it is a clustered index.
key_buffer_size is probably for MyISAM only.

2) this is the current configuration
mysql> show variables like '%buf%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 536870912 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 67108864 |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| read_buffer_size | 2093056 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
+-------------------------+-----------+
10 rows in set (0.00 sec)

3) Change the > into >= and see if the explain will change somehow. The explain shows no difference.

mysql> explain select MIN(recordnumber) from oaa_account where recordnumber>66728314 and recordtype=0;
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
| oaa_account | range | PRIMARY | PRIMARY | 8 | NULL | 189142 | Using where; Using index |
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.01 sec)

mysql> explain select MIN(recordnumber) from oaa_account where recordnumber>=66728314 and recordtype=0;
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
| oaa_account | range | PRIMARY | PRIMARY | 12 | NULL | 189142 | Using where; Using index |
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

4) I have already run optimize and analyze table

5) Using "LIMIT 1" makes no difference

mysql> explain select MIN(recordnumber) from oaa_account where recordnumber>6672
8314 and recordtype=0 LIMIT 1;
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
| oaa_account | range | PRIMARY | PRIMARY | 8 | NULL | 189142 | Using where; Using index |
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

6) In my opinion there is a bug in MySQL when processing MIN(col1) and the table is sorted ascending by col1.