fast way to retrieve number of rows in a big table
Tue, 05/24/2011 - 09:02
In investigation with big tables survival I found a fast way to retrieve total rows of a table (my test table is telefonichiamate with 3909999 rows )
SHOW TABLE STATUS like 'telefonichiamate';this sql line took 0,3 seconds to replay a single row (here is a plist of row)
<key>Name</key>
<string>telefonichiamate</string>
<key>Engine</key>
<string>InnoDB</string>
..
<key>Rows</key>
<integer>3909999</integer>
<key>Avg_row_length</key>
<integer>212</integer>
<key>Data_length</key>
<integer>830472192</integer>
..
<key>Comment</key>
<string>InnoDB free: 1183744 kB</string>SQL Command can be translated in qcubed in this way
$objDatabase = QApplication::$Database[1];
$strQuery = "SHOW TABLE STATUS like 'telefonichiamate'";
$objDbResult = $objDatabase->Query($strQuery);
$mixRow = $objDbResult->FetchArray();
$this->nrec=$mixRow['Rows'];The traditional
$this->nrec = Telefonichiamate::QueryCount(QQ::All());took 186 seconds ti give response of 3930989.
I admit that the two infos are slightly different - perhaps deleted rows ????
(but the time used also ....)
Hope this can help Qcubed (qcodo) user community ... and ... ported to _core to improve
DATAGRID response and pagination in opening big tables
Suggestion ... limit to 10000 rows and ask for where condition to limit array given to
DataBinder
Gianni
This number is not always accurate.
Taken from the MySQL documentation (http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html):
-----------------------------------------------------------------------
#Rows
The number of rows. Some storage engines, such as MyISAM, store the exact count. 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.
The Rows value is NULL for tables in the INFORMATION_SCHEMA database.
-----------------------------------------------------------------------
I admit that inaccuracy in my initial post! ... but for very big table the first panel of a list (from drafts) take very.. very.. long time to appear ... so some trick is mandatory!!!! to be 'ergonomic'.
My approach is: test if a table is big (in rapid way) and limit the datasource overriding the setdatabinder function.
( I've learnt this method to have rows value from the approach used in PhpMyAdmin).
Ciao, Gianni