Most efficient way to get table row count

Following is the most performant way to find the next AUTO_INCREMENT value for a table. This is quick even on databases housing millions of tables, because it does not require querying the potentially large information_schema database.

mysql> SHOW TABLE STATUS LIKE 'table_name';
// Look for the Auto_increment column

However, if you must retrieve this value in a query, then to the information_schema database you must go.

SELECT `AUTO_INCREMENT`
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA = 'DatabaseName'
AND    TABLE_NAME   = 'TableName';

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

I'm not sure why no one has suggested the following. This will get the auto_increment value using just SQL (no need for using PHP's mysql_fetch_array):

SELECT AUTO_INCREMENT FROM information_schema.tables WHERE TABLE_NAME = 'table'

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]";
?>