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 columnHowever, if you must retrieve this value in a query, then to the information_schema database you must go.
SELECT `AUTO_INCREMENT`FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'DatabaseName'AND TABLE_NAME = 'TableName';