I have a very large table with over 1,300,000 entries.
I need a very fast method of obtaining the number of rows in the table. The table is indexed and highly optmized but the following code still takes about 5 to 10 seconds to return a result.
I noticed that phpMyAdmin can return the number of rows pretty much instantly so I am assuming there is a faster way to do it.
Although indirectly what phpMyAdmin does, that I remember is not exaclty how phpMyAdmin does it. phpMyAdmin reads multiple values from the table properties, one of which is number of records.
Thank you, the speed difference is quite significant.
One question though, is it necessary to call mysql_free_result();? I am assuming that only an integer would be in memory after the count(*) query. Am I wrong? Does the $result resource contain more than that?
Call mysql_free_result() as soon as possible in every one of your scripts, in this case yes the memory being used is trivial but it is principle.
Feyd, that makes sense that phpmyadmin would go that route, but count() makes sense in this situation -
For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.
yeah.. I only remember the status check.. but they may have hidden the count check further in the code.. it was a while ago that I last read through the code.
To clarify, if I have a MySQL query inside of a function I should free the memory before the function returns the result? The Memory is not released automatically?
Also, a lot of times I re-use the $result variable. Would that free the memory being used by the previous query?
Oh ok, so the sole purpose of that is to help the MySQL database run more efficently? So then I really should use mysql_free_result() as much as possible, especially on high traffic sites. Is that right?