MySQL - It is possible to speed up MySQL count?

Question

I have a simple query like:

select count(*) from table

Can I speed up MySQL by doing:

select count(id) from table

Does this make any difference in terms of speed?

Best Regards,

http://stackoverflow.com/questions/1031312/how-to-speed-up-select-count-with-group-by-and-where — LostMohican, Nov 28 '11 at 11:05
http://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count — esskar, Nov 28 '11 at 11:08

score 1 · Accepted Answer · answered Nov 28 '11 at 11:05

1

Not if id is your primary key. * will simply map to your primary key. count(id) = count(*)

answered Nov 28 '11 at 11:05

Tom van der Woerdt

29,532
7
72
105

score 1 · Answer 2 · answered Nov 28 '11 at 11:06

1

You can try to get the count from information_schema

select TABLE_ROWS from information_schema.tables
where TABLE_SCHEMA = '$db' and TABLE_NAME = '$tbl';

answered Nov 28 '11 at 11:06

ajreal

46,720
11
89
119

Wouldn't MySql use these tables under the covers on a count(*) anyway? – Rich O'Kelly Nov 28 '11 at 11:10
count can combine with filter, group by ... but this information_schema.tables is a plain count of rows in table – ajreal Nov 28 '11 at 11:12

score 0 · Answer 3 · answered Nov 28 '11 at 11:08

0

If id is your primary key, then no it will not, since MySql will use the primary key under the covers anyway. If id is not the primary key, then it may actually make the query slightly slower.

answered Nov 28 '11 at 11:08

Rich O'Kelly

41,274
9
83
114

score 0 · Answer 4 · answered Nov 28 '11 at 22:23

0

If id is "not null", no it won't make any difference.

If id is nullable, then it is a different query and will not (always) produce the same result.

answered Nov 28 '11 at 22:23

MarkR

62,604
14
116
151

MySQL - It is possible to speed up MySQL count?

4 Answers4