MySQL Forums
Forum List  »  Optimizer & Parser

Re: MIN on an indexed field too slow
Posted by: Camelia Ticau
Date: March 01, 2006 04:54PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
4164
March 01, 2006 01:58PM
2297
March 01, 2006 03:25PM
Re: MIN on an indexed field too slow
2176
March 01, 2006 04:54PM
2158
March 01, 2006 05:19PM
1979
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.