[nycphp-talk] MySQL doubt
Tim Gales
tgales at tgaconnect.com
Sat Jan 15 12:22:00 EST 2005
Ophir Prusak writes:
> Why doesn't MySQL use the primary key in the second query?
> I would think that count doesn't care what column I use, but
> obviously it does.
In the first query, the MySQL optimizer recognizes that it can get
everything it needs from the index -- and uses it.
Notice the difference between types: range vs. ALL
In the MySQL manual it says:
"The definition of a range condition for a single-part
index is as follows:
For both BTREE and HASH indexes, comparison of a key part with
a constant value is a range condition when using the =, <=>, IN,
IS NULL, or IS NOT NULL operators"
http://dev.mysql.com/doc/mysql/en/Range_access_single-part.html
It would seem that the optimizer either got befuddled
by bad statistics or it decided that using the index would require
more seeks to read both the index and the table versus just
reading 'all' of the table.
You could coerce the optimizer to go with range by adding
a limit.
Try explaining "select count(rev_title) from review where
rev_id > 10000 limit 30000"
(just for fun try explaining with a limit greater than the
total number of rows in the table)
You might want to try benchmarking with and without
the limit of 30000 -- to see if the optimizer
made a good choice.
T. Gales & Associates
'Helping People Connect with Technology'
http://www.tgaconnect.com
> -----Original Message-----
> From: talk-bounces at lists.nyphp.org
> [mailto:talk-bounces at lists.nyphp.org] On Behalf Of
> Sent: Friday, January 14, 2005 9:16 PM
> To: NYPHP Talk
> Subject: Re: [nycphp-talk] MySQL doubt
>
>
> As long as we're on the subject, could someone shed some
> light on this behavior? It caused me a good amount of hair
> pulling until I realized what mysql was doing.
>
>
> mysql> explain select count(rev_id) from review where rev_id > 10000;
> +--------+-------+---------------+---------+---------+------+-
> ------+-------------------------+
> | table | type | possible_keys | key | key_len | ref | rows |
> Extra |
> +--------+-------+---------------+---------+---------+------+-
> ------+-------------------------+
> | review | range | PRIMARY | PRIMARY | 4 | NULL | 30608 |
> where used; Using index |
> +--------+-------+---------------+---------+---------+------+-
> ------+-------------------------+
> 1 row in set (0.00 sec)
>
> mysql> explain select count(rev_title) from review where
> rev_id > 10000;
> +--------+------+---------------+------+---------+------+-----
> --+------------+
> | table | type | possible_keys | key | key_len | ref |
> rows | Extra |
> +--------+------+---------------+------+---------+------+-----
> --+------------+
> | review | ALL | PRIMARY | NULL | NULL | NULL |
> 43894 | where used |
> +--------+------+---------------+------+---------+------+-----
> --+------------+
> 1 row in set (0.00 sec)
>
> mysql> desc review;
> +----------------+--------------+------+-----+------------+---
> -------------+
> | Field | Type | Null | Key | Default |
> Extra |
> +----------------+--------------+------+-----+------------+---
> -------------+
> | rev_id | int(11) | | PRI | NULL |
> auto_increment |
> | rev_usr_id | int(11) | | MUL | 0 |
> |
> | rev_gsy_id | int(11) | | MUL | 0 |
> |
> | rev_text | text | YES | | NULL |
> |
> | rev_status | char(1) | YES | | NULL |
> |
> | rev_regdate | date | | MUL | 0000-00-00 |
> |
> | rev_email | varchar(255) | YES | | NULL |
> |
> | rev_title | varchar(255) | YES | | NULL |
> |
> | rev_score | int(11) | YES | | NULL |
> |
> | rev_from | varchar(255) | YES | | NULL |
> |
> | rev_name | varchar(255) | YES | | NULL |
> |
> | rev_show_name | tinyint(4) | YES | | NULL |
> |
> | rev_show_email | tinyint(4) | YES | | NULL |
> |
> | rev_notes | text | YES | | NULL |
> |
> | help_yes | int(11) | | | 0 |
> |
> | help_no | int(11) | | | 0 |
> |
> +----------------+--------------+------+-----+------------+---
> -------------+
> 16 rows in set (0.00 sec)
>
> On Fri, 14 Jan 2005 10:53:11 +0530, Sajith A
> <sajith.ml at gmail.com> wrote:
> > I'm not sure whether i should post this here. Correct me if i'm
> > wrong.. I would like to know whether there is any performance
> > difference between. "SELECT count(*) FROM table" and "SELECT
> > count(field) FROM table".
> > _______________________________________________
> > New York PHP Talk
> > Supporting AMP Technology (Apache/MySQL/PHP)
> > http://lists.nyphp.org/mailman/listinfo/talk
> > http://www.nyphp.org
> >
> _______________________________________________
> New York PHP Talk
> Supporting AMP Technology (Apache/MySQL/PHP)
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org
>
More information about the talk
mailing list