5

When writing a query for paging on a web page what is the least expensive method to get a total row count? Is there a way to do this without running a query twice - one for the total and the next for the limit?

Using MySQL

Example: (I want to know if there is a less expensive way)

Get Count

SELECT COUNT(*) FROM table

Get Paging

SELECT mycolumns FROM table LIMIT 100

How can I get the total count without running 2 queries.

Todd Moses
  • 10,969
  • 10
  • 47
  • 65
  • try pre-counting the table size and store the value and run a background process to update the values from time to time – Jonathan Lin Jan 09 '13 at 10:05

4 Answers4

4

You can run the first query with the SQL_CALC_FOUND_ROWS option, and then run this:

SELECT FOUND_ROWS();

http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_found-rows

Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • Excellent - Is this less expensive then the 2 queries? – Todd Moses Feb 26 '10 at 19:11
  • I can't guarantee that it will be *less* expensive than 2 queries in all cases, but it certainly shouldn't be *more* expensive. The comparison will depend on lots of factors: storage engine(s), size of table, columns being selected, where clause, index vs. full scan, etc. – Ike Walker Feb 26 '10 at 19:39
  • In short, if you are worried about performance you may want to do some testing with your specific data. – Ike Walker Feb 26 '10 at 19:40
1

This will give you an additional column called Count in each row that contains the total number of rows:

SELECT mycolumns, (select count(*) from table) as Count 
FROM table 
LIMIT 100
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • Is this more efficient than the two queries? – Todd Moses Feb 26 '10 at 19:20
  • 1
    This will be more efficient than separate queries as you are only making one call to the database from your application. – D'Arcy Rittich Feb 26 '10 at 19:22
  • Although this question is marked with the tags SQL, and MySQL, the above solution will only work in some SQL variants, such as MySQL. Other variants will require a GROUP BY clause. – asnyder Mar 29 '11 at 22:42
0

select count(*) from tablename

Is the most efficient way to get the number of rows in table tablename

Martin
  • 7,089
  • 3
  • 28
  • 43
  • 3
    "Is there a way to do this without running a query twice" – thetaiko Feb 26 '10 at 19:06
  • why would you have to run this (or mine) query twice? – Jay Feb 26 '10 at 19:08
  • 1
    Because one for the count and the second for the paging (limit) so that only x amount of rows are returned to the page. You have problem seen the 1 | 2 | 3 ... Last at the bottom of pages. – Todd Moses Feb 26 '10 at 19:12
0
select count(1) as counter from *table_name*

this is slighter better than using count(*) as your avoiding checking all the columns in the table.

Jay
  • 4,994
  • 4
  • 28
  • 41
  • Good idea for the first query but how do I combine the queries – Todd Moses Feb 26 '10 at 19:11
  • 3
    there is no difference between count(*) and count(1): http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156159920245 http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156151916789 – Ty W Feb 26 '10 at 19:12
  • This answer getting upvoted makes me very sad. There is so much material out there that proves beyond a shadow of a doubt that count(1) is equivalent to count(*). There is no reason for this rumor to persist. http://stackoverflow.com/questions/1221559/count-vs-count1 – Ty W Feb 26 '10 at 20:32