NYCPHP Meetup

[Tim Gales]

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
>