Hi all, Just a quick question. I have a PHP script which returns a row count of a single table - it is for statistical purposes. Currently the table holds 2.7 million rows with about 120,000 being added every single day. In total the table is almost 150MB in size. When the figure was below a million loading the page was fine but now it's getting really slow as the number increases. Can take anywhere up to 30 seconds to load the page. My question is, what is the most efficient way of getting an accurate row count on the table? e.g. SELECT COUNT(*) versus mysql_num_rows() Any other options are welcome!
SELECT COUNT(*) is normally faster then mysql_num_rows, especially in larger tables. mysql_num_rows would require the database to setup the results, when SELECT COUNT(*) can be taken directly from the table information stuff. At least, I think it is like this.
I agree with SKY Kalashnikov D)! The fastest way to get the number of rows in a table is doing this: $total_rows = mysql_result(mysql_query("SELECT COUNT(id) FROM your_table"),0); Code (markup): As long as there are no NULL id's, it will return the correct rows extremely fast. If you already used your_table though, it is faster to use mysql_num_rows() on the result of it...