f



Using where; Using index vs Using where

Hello,

What are the difference between the following statements, which one is
better?


 mysql> desc SELECT COUNT(*) as `cnt` FROM vote WHERE `ip` =
INET_ATON('127.0.0.1') AND (`addtime` > NOW() - INTERVAL 1 DAY) ;
+----+-------------+-------+-------+---------------+---------------
+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key           |
key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------------
+---------+------+------+--------------------------+
|  1 | SIMPLE      | vote  | range | IX_ip_addtime | IX_ip_addtime
|       8 | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------------
+---------+------+------+--------------------------+
1 row in set (0.01 sec)

mysql> desc SELECT COUNT(id) as `cnt` FROM vote WHERE `ip` =
INET_ATON('127.0.0.1') AND (`addtime` > NOW() - INTERVAL 1 DAY) ;
+----+-------------+-------+-------+---------------+---------------
+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key           |
key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------
+---------+------+------+-------------+
|  1 | SIMPLE      | vote  | range | IX_ip_addtime | IX_ip_addtime
|       8 | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------+---------------
+---------+------+------+-------------+
0
howachen (521)
10/24/2008 3:08:55 AM
comp.databases.mysql 6944 articles. 1 followers. laredotornado (869) is leader. Post Follow

4 Replies
1933 Views

Similar Articles

[PageSpeed] 35

On Oct 23, 11:08=A0pm, howa <howac...@gmail.com> wrote:
> Hello,
>
> What are the difference between the following statements, which one is
> better?
>
> =A0mysql> desc SELECT COUNT(*) as `cnt` FROM vote WHERE `ip` =3D

COUNT(*) counts all rows selected (effectively).

> mysql> desc SELECT COUNT(id) as `cnt` FROM vote WHERE `ip` =3D

COUNT(col) counts only rows where col is not NULL.
0
toby23 (1177)
10/24/2008 4:15:11 PM
Hello,

On Oct 25, 12:15=A0am, toby <t...@telegraphics.com.au> wrote:
> On Oct 23, 11:08=A0pm, howa <howac...@gmail.com> wrote:
>
> COUNT(col) counts only rows where col is not NULL.

Yes, but in the explain statement, if using count(*), it show

>> Using where; Using index

if using count(id), it show

>> Using where


Which one is better? (Suppose the id should not be NULL anyway)
0
howachen (521)
10/24/2008 4:38:26 PM
On Oct 24, 12:38=A0pm, howa <howac...@gmail.com> wrote:
> Hello,
>
> On Oct 25, 12:15=A0am, toby <t...@telegraphics.com.au> wrote:
>
> > On Oct 23, 11:08=A0pm, howa <howac...@gmail.com> wrote:
>
> > COUNT(col) counts only rows where col is not NULL.
>
> Yes, but in the explain statement, if using count(*), it show
>
> >> Using where; Using index
>
> if using count(id), it show
>
> >> Using where
>
> Which one is better? (Suppose the id should not be NULL anyway)

This sounds like micro-optimisation to me.
0
toby23 (1177)
10/24/2008 7:55:43 PM
howa wrote:

> Which one is better? (Suppose the id should not be NULL anyway)

As Toby has already said, the difference will likely be nothing at all
in practice, though if there were a difference, I'd expect the one
without the index listed (the count of id) to be the slower. Similarly
if the id column isn't specified as NOT NULL.

However, I think it is far more important to write a query that means
what you want it to mean, rather than something that happens to mean
what you want it to mean in normal circumstances. That is to say the
COUNT(*) is the correct query to use if you actually want just the row
count. It isn't slower because it is collecting data from each column,
that's not how it works. Trying to speed things up by just specifying
one column wont provide any benefits and, as Toby pointed out, could
actually return a different result set if you weren't careful. Best look
at correctness first, then speed. But anyway, COUNT(*) would be the
faster if either of them actually turned out to be.
0
tn214 (43)
10/28/2008 12:04:56 PM
Reply: