Get Auto Increment value with MySQL query

Question

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.

Answer 1

SELECT Auto_increment 
FROM information_schema.tables 
WHERE table_name='the_table_you_want';

Answer 2

try using the field name in the function

select LAST_INSERT_ID(field) from table limit 1;

but LAST_INSERT_ID() should have worked

Answer 3

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)

Answer 4

try this

Execute this SQL : SHOW TABLE STATUS LIKE ''

and fetch the value of the field "Auto_increment"

Answer 5

$result = mysql_query("SHOW TABLE STATUS LIKE 'table_name'");
$row = mysql_fetch_array($result);
$nextId = $row['Auto_increment'];
mysql_free_result($result);

This solution is quick even on databases housing thousands (or millions) of tables, because it does not require querying the incredible large information_schema database.