MySQL Forums
Forum List  »  Optimizer & Parser

Re: MIN on an indexed field too slow
Posted by: Camelia Ticau
Date: March 02, 2006 11:38AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
4160
March 01, 2006 01:58PM
2296
March 01, 2006 03:25PM
2155
March 01, 2006 05:19PM
Re: MIN on an indexed field too slow
2040
March 02, 2006 11:38AM
1976
March 02, 2006 03:28PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.