Well, actually, there are 2.878 Meg rows, or 2878k. What's happening is that it's using the index to find all of the rows where changed > 0, then scanning for the maximum. If you just look for the maximum, then discard it if it's not greater than 0, it will be much faster.
The following with InnoDB tables. (MyISAM has the maximum pre-calculated, so it should be even faster.) mysql> SELECT MAX(changed) FROM archived_stats where changed>0; +--------------+ | MAX(changed) | +--------------+ | 99998 | +--------------+ 1 row in set (0.21 sec) mysql> SELECT MAX(changed) as maximum FROM archived_stats having maximum > 0; +---------+ | maximum | +---------+ | 99998 | +---------+ 1 row in set (0.00 sec) mysql> explain SELECT MAX(changed) FROM archived_stats where changed>0; +----------------+-------+---------------+---------+---------+------+------- +--------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +----------------+-------+---------------+---------+---------+------+------- +--------------------------+ | archived_stats | range | changed | changed | 4 | NULL | 52802 | Using where; Using index | +----------------+-------+---------------+---------+---------+------+------- +--------------------------+ mysql> explain SELECT MAX(changed) as maximum FROM archived_stats having maximum > 0; +------------------------------+ | Comment | +------------------------------+ | Select tables optimized away | +------------------------------+ You could also use the following. The idea is to get MySQL to start from the highest thing in the index, not the lowest. mysql> SELECT changed FROM archived_stats where changed>0 order by changed desc limit 1; +---------+ | changed | +---------+ | 99998 | +---------+ 1 row in set (0.00 sec) mysql> explain SELECT changed FROM archived_stats where changed>0 order by changed desc limit 1; +----------------+-------+---------------+---------+---------+------+------- +--------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +----------------+-------+---------------+---------+---------+------+------- +--------------------------+ | archived_stats | range | changed | changed | 4 | NULL | 52802 | Using where; Using index | +----------------+-------+---------------+---------+---------+------+------- +--------------------------+ 1 row in set (0.00 sec) ========== original message follows ========== From: Egor Egorov <[EMAIL PROTECTED]> Date: Mon, 06 Sep 2004 13:02:11 +0300 Subject: Re: please explain why this query isn't optimized To: [EMAIL PROTECTED] Dave Dyer <[EMAIL PROTECTED]> wrote: > Before I post it as a bug, perhaps someone can explain why > this query is not optimized to use the index (it has to examine all 287k rows). > > mysql> explain SELECT MAX(changed) FROM archived_stats where changed>0; > | table | type | possible_keys | key | key_len | ref | rows | Extra | > | archived_stats | range | changed | changed | 4 | NULL | 2878820 | Using where; Using index | There are 2.878k rows, not 287k. And it's optimized as much as possible, the key is used. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
