Fastest way to get number or rows of 500 000 rows?

[smagdy]

Hello,

I am displaying the results 50/page of 500 000

So to get number of rows using select count(*) then do another select to get 50 rows out of 500 000 gets somehow slow..

When i tried to put the total count as fixed number and not executing that first select... it went so fast..

So i want to get number of rows by any other fast way!

The number of rows can change anytime the user submit a form which can happen few times/hour..

Is it a good idea to make like crons job every 30min to calc the number of rows and keep it in another table, so that i just select it when i need it instead of doing full table scan everytime?

Any suggestions are appreciated, thanks in advance!

[mcavic]

Select count(*) should be fast. I tried it on a table with 105 million rows, and it returned instantly. That's because MySQL always keeps track of the total number of records in the table.

Are you using any where clauses? If so, check to make sure the table is indexed properly.

[hughie]

Sounds like something isn't correct, SELECT COUNT is very fast indeed, no matter the number of rows.

Sounds to me like you may be selecting the whole dataset rather than just the COUNT value.

try:
$result=mysql_query("SELECT COUNT(*) FROM mytablename");
$resArray=mysql_fetch_row($result);
$numrows=$resArray[0];

Cheers,
hughie

[alfaguru]

What storage method are you using? IIRC count(*) is much slower on INNODB than MYISAM.