Fastest way to get number of records in mysql

Question

Is there a faster method to get the record number of a huge mysql table (~3 mil rows) ?

I don't want to list them, only to get the total.

Thanks

Same question answered here: http://stackoverflow.com/questions/5060366/mysql-fastest-way-to-count-number-of-rows — HRgiger, Sep 19 '11 at 16:33

score 3 · Accepted Answer · answered Sep 19 '11 at 16:17

3

SELECT COUNT(id) AS total FROM table;

answered Sep 19 '11 at 16:17

usoban

oh, it looks i need to count the same field in the where argument. Thanks. – keepwalking Sep 19 '11 at 16:27

Bluewind · Answer 2 · 2011-09-19T16:33:53.693

0

SELECT COUNT(id) FROM tbl;

If "id" is an index this should be pretty fast.

If you use a WHERE too, the index has to be a covering index (contain all rows in one index)

edited Sep 19 '11 at 16:33

answered Sep 19 '11 at 16:19

Bluewind

The ID is the primary index and is still very slow. 1 row in set (3.45 sec) – keepwalking Sep 19 '11 at 16:23

score 0 · Answer 3 · answered Sep 19 '11 at 16:20

0

You could just say:

SELECT COUNT(*) FROM TABLE;

That should display the number of records in the table.

answered Sep 19 '11 at 16:20

Faheem

3 Answers3