I currently have a database with over 6 million rows and growing. I currently do SELECT COUNT(id) FROM table; in order to display the number to my users, but the database is getting large and I have no need to store all of those rows except to be able to show the number. Is there a way to select the auto_increment value to display so that I can clear out most of the rows in the database? Using LAST_INSERT_ID() doesn't seem to work.
Most efficient way to get table row count
Viewed 115093
12 Answers
If it's only about getting the number of records (rows) I'd suggest using:
SELECT TABLE_ROWS
FROM information_schema.tables
WHERE table_name='the_table_you_want' -- Can end here if only 1 DB
AND table_schema = DATABASE(); -- See comment below if > 1 DB
(at least for MySQL) instead.
try this
Execute this SQL:
SHOW TABLE STATUS LIKE '<tablename>'
and fetch the value of the field Auto_increment
if you directly get get max number by writing select query then there may chance that your query will give wrong value. e.g. if your table has 5 records so your increment id will be 6 and if I delete record no 5 the your table has 4 records with max id is 4 in this case you will get 5 as next increment id. insted to that you can get info from mysql defination itself. by writing following code in php
<?
$tablename = "tablename";
$next_increment = 0;
$qShowStatus = "SHOW TABLE STATUS LIKE '$tablename'";
$qShowStatusResult = mysql_query($qShowStatus) or die ( "Query failed: " . mysql_error() . "<br/>" . $qShowStatus );
$row = mysql_fetch_assoc($qShowStatusResult);
$next_increment = $row['Auto_increment'];
echo "next increment number: [$next_increment]";
?>
Next to the information_schema suggestion, this:
SELECT id FROM table ORDER BY id DESC LIMIT 1
should also be very fast, provided there's an index on the id field (which I believe must be the case with auto_increment)
Related