Re: MIN on an indexed field too slow

[Camelia Ticau]

Thank you for addressing my problem

1) there should be no difference if WHERE clause is "WHERE col1 > xxx and col2 = yyy" or
"WHERE col2=yyy and col1 > xxx". Is that true?

2) the results for the query proposed and the explain is below
# Time: 060301 17:29:29
# User@Host: run[run] @ localhost []
# Query_time: 182 Lock_time: 0 Rows_sent: 1 Rows_examined: 271546
SELECT MIN(recordNumber) FROM oaa_account WHERE recordNumber > 66728314 AND reco
rdType = 0;

mysql> explain SELECT MIN(recordNumber) FROM oaa_account WHERE recordNumber > 66
728314 and recordtype=0;
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
| oaa_account | range | PRIMARY | PRIMARY | 8 | NULL | 189504 | Using where; Using index |
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)


3) explain for the previous query is:
mysql> explain SELECT MIN(recordNumber) FROM oaa_account WHERE recordtype=0 and
recordnumber>66728314;
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
| oaa_account | range | PRIMARY | PRIMARY | 8 | NULL | 189504 | Using where; Using index |
+-------------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)


4) the counter is matching the number of rows analyzed by MySQL
mysql> SELECT COUNT(*) FROM oaa_account WHERE recordNumber > 66728314;
+----------+
| COUNT(*) |
+----------+
| 271546 |
+----------+
1 row in set (1.18 sec)

5)

The records in the table are:
.................
(66728314, 0)
(66728318, 0)
(66728322, 0)
(66728324, 0)
....................
If I would execute the query manually, I would start by analyzing the next records after (66728314,0) - as MySQL does. But then I would stop the search at (66728318, 0), as my records are sorted in the order (recordnumber, recordtype).

I undestand the MySQL decides to parse all the next records after 66728314 (in number of 271546 ). Because the index is (recordnumber, recordtype), MySQL should stop the search with the first next record (66728318, 0) because the recordtype is 0, satisfying the WHERE condition.

Can you please explain this?

Thank you,
Camelia