List:General Discussion« Previous MessageNext Message »
From:Bill Easton Date:September 6 2004 2:40pm
Subject:Re: please explain why this query isn't optimized
View as plain text  
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 <egor.egorov@stripped>
Date: Mon, 06 Sep 2004 13:02:11 +0300
Subject: Re: please explain why this query isn't optimized
To: mysql@stripped

Dave Dyer <ddyer@stripped> 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.

Thread
please explain why this query isn't optimizedDave Dyer4 Sep
  • RE: please explain why this query isn't optimizedDonny Simonton4 Sep
    • Re: please explain why this query isn't optimizedEric Bergen4 Sep
    • RE: please explain why this query isn't optimizedDave Dyer4 Sep
      • Re: please explain why this query isn't optimizedDan Nelson4 Sep
        • Re: please explain why this query isn't optimizedDave Dyer4 Sep
          • RE: please explain why this query isn't optimizedDonny Simonton4 Sep
  • Re: please explain why this query isn't optimizedEgor Egorov6 Sep
Re: please explain why this query isn't optimizedBill Easton6 Sep
  • Re: please explain why this query isn't optimizedDavid T-G7 Sep
    • Re: please explain why this query isn't optimizedEgor Egorov7 Sep
  • Re: please explain why this query isn't optimizedEgor Egorov7 Sep