3

After facing a slow loading time issue with a mysql query, I'm now looking the best way to count rows numbers. I have stupidly used mysql_num_rows() function to do this and now realized its a worst way to do this. I was actually making a Pagination to make pages in PHP. I have found several ways to count rows number. But I'm looking the faster way to count it.

The table type is MyISAM

So the question is now

Which is the best and faster to count - 

1. `SELECT count(*) FROM 'table_name'`

2. `SELECT TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema =  'database_name'
AND table_name LIKE  'table_name'`

3. `SHOW TABLE STATUS LIKE 'table_name'`

4. `SELECT FOUND_ROWS()`

If there are others better way to do this, please let me know them as well. If possible please describe along with the answer- why it is best and faster. So I could understand and can use the method based on my requirement.

Thanks.

Community
  • 1
  • 1
Aajahid
  • 1,619
  • 2
  • 13
  • 33
  • Did you just try using "Limit"? and paginate accordingly at run time??i.e., if a user clicks "next 50" show him/her the next 50 items? and so on... – PhD Aug 04 '11 at 10:03
  • I'm actually trying to make regular pagination. Where it will be shown like - **Showing Page 1 of 4739**. I will sure use LIMIT for displaying result, but I need to know the rows number to make accurate page numbers and display it. – Aajahid Aug 04 '11 at 10:07
  • Can you post the times for each of these four options? – jmilloy Aug 04 '11 at 11:50

5 Answers5

8

Quoting the MySQL Reference Manual on COUNT

COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:

mysql> SELECT COUNT(*) FROM student;

This optimization applies only to MyISAM tables only, because an exact row count is stored for this storage engine and can be accessed very quickly. For transactional storage engines such as InnoDB, storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count.

Also read this question MySQL - Complexity of: SELECT COUNT(*) FROM MyTable;

Community
  • 1
  • 1
Ranhiru Jude Cooray
  • 19,542
  • 20
  • 83
  • 128
5

I would start by using SELECT count(*) FROM 'table_name' because it is the most portable, easiset to understand, and because it is likely that the DBMS developers optimise common idiomatic queries of this sort.

Only if that wasn't fast enough would I benchmark the approaches you list to find if any were significantly faster.

RedGrittyBrick
  • 3,827
  • 1
  • 30
  • 51
2

It's slightly faster to count a constant:

select count('x') from table;

When the parser hits count(*) it has to go figure out what all the columns of the table are that are represented by the * and get ready to accept them inside the count().

Using a constant bypasses this (albeit slight) column checking overhead.

As an aside, although not faster, one cute option is:

select sum(1) from table;
Bohemian
  • 412,405
  • 93
  • 575
  • 722
1

I've looked around quite a bit for this recently. it seems that there are a few here that I'd never seen before.

Special needs: This database is about 6 million records and is getting crushed by multi-insert queries all the time. Getting a true count is difficult to say the least.

SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE table_schema =  'admin_worldDomination' AND table_name LIKE  'master'

Showing rows 0 - 0 ( 1 total, Query took 0.0189 sec)

This is decent, Very fast but inaccurate. Showed results from 4 million to almost 8 million rows

SELECT count( * ) AS counter FROM `master`

No time displayed, took 8 seconds real time. Will get much worse as the table grows. This has been killing my site previous to today.

SHOW TABLE STATUS LIKE 'master'

Seems to be as fast as the first, no time displayed though. Offers lots of other table information, not much of it is worth anything though (avg record length maybe).

SELECT FOUND_ROWS() FROM 'master'

Showing rows 0 - 29 ( 4,824,232 total, Query took 0.0004 sec)

This is good, but an average. Closer spread than others (4-5 million) so I'll probably end up taking a sample from a few of these queries and averaging.

EDIT: This was really slow when doing a query in php, ended up going with the first. Query runs 30 times quickly and I take an average, under 1 second ... it' still ranges between 5.3 & 5.5 million

Josh Storz
  • 11
  • 2
0

One idea I had, to throw this out there, is to try to find a way to estimate the row count. Since it's just to give your user an idea of the number of pages, maybe you don't need to be exact and could even say Page 1 of ~4837 or Page 1 of about 4800 or something.

I couldn't quickly find an estimate count function, but you could try getting the table size and dividing by a determined/constant avg row size. I don't know if or why getting the table size from TABLE STATUS would be faster than getting the rows from TABLE STATUS.

jmilloy
  • 7,875
  • 11
  • 53
  • 86