[mysql] 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.

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

}

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

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.

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';

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]);

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 :)

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'

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

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