Most efficient way to get table row count

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.

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

SELECT id FROM table ORDER BY id DESC LIMIT 1 can returns max id not auto increment id. both are different in some conditions

If you do not have privilege for "Show Status" then, The best option is to, create two triggers and a new table which keeps the row count of your billion records table.

Example:

TableA >> Billion Records
TableB >> 1 Column and 1 Row

Whenever there is insert query on TableA(InsertTrigger), Increment the row value by 1 TableB
Whenever there is delete query on TableA(DeleteTrigger), Decrement the row value by 1 in TableB

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)

$next_id = mysql_fetch_assoc(mysql_query("SELECT MAX(id) FROM table"));
$next_id['MAX(id)']; // next auto incr id

hope it helpful :)

Controller

SomeNameModel::_getNextID($this->$table)

MODEL

class SomeNameModel extends CI_Model{

private static $db;

function __construct(){
  parent::__construct();
  self::$db-> &get_instance()->db;
}


function _getNextID($table){
  return self::$db->query("SHOW TABLE STATUS LIKE '".$table."' ")->row()->Auto_increment;
}

... other stuff code

}

None of these answers seem to be quite right. I tried them all. Here are my results.

Sending query: SELECT count(*) FROM daximation
91
Sending query: SELECT Auto_increment FROM information_schema.tables WHERE table_name='daximation'
96
Sending query: SHOW TABLE STATUS LIKE 'daximation'
98
Sending query: SELECT id FROM daximation ORDER BY id DESC LIMIT 1
97

here's the screenshot: https://www.screencast.com/t/s8c3trYU

Here is my PHP code:

$query = "SELECT count(*) FROM daximation"; 
$result = sendquery($query);
$row = mysqli_fetch_row($result);
debugprint( $row[0]);

$query = "SELECT Auto_increment FROM information_schema.tables WHERE table_name='daximation'"; 
$result = sendquery($query);
$row = mysqli_fetch_row($result);
debugprint( $row[0]);

$query = "SHOW TABLE STATUS LIKE 'daximation'"; 
$result = sendquery($query);
$row = mysqli_fetch_row($result);
debugprint( $row[10]);

$query = "SELECT id FROM daximation ORDER BY id DESC LIMIT 1"; 
$result = sendquery($query);
$row = mysqli_fetch_row($result);
debugprint( $row[0]);

Couldn't you just create a record in a separate table or whatever with a column called Users and UPDATE it with the last inserted id on User Registration?

Then you would just check this field with a simple query.

It might be rough but it would work perfectly.