NYCPHP Meetup

[Ophir Prusak]

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.

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.

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
>