Most efficient way to get table row count

by

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

More Related Contents:

  1. Complex Count in MySQL
  2. Order by COUNT per value
  3. How to get the count of each distinct value in a column? [duplicate]
  4. INSERT INTO SELECT statement with more columns which is not in another table
  5. MySQL how to fill missing dates in range?
  6. Left Outer Join doesn’t return all rows from my left table?
  7. ‘IF’ in ‘SELECT’ statement – choose output value based on column values
  8. 1052: Column ‘id’ in field list is ambiguous
  9. How to split the name string in mysql?
  10. Can a foreign key reference a non-unique index?
  11. sql join two table
  12. Compare dates in MySQL
  13. Cast int to varchar
  14. SQL query of multiple values in one cell
  15. Import SQL file into mysql
  16. MySQL SELECT last few days?
  17. “where 1=1” statement [duplicate]
  18. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  19. how to cast the hexadecimal to varchar(datetime)?
  20. Is there a way to get the row number in Mysql like the rownum in oracle [duplicate]
  21. How to get a list of months between two dates in mysql
  22. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  23. Postgres LEFT JOIN with WHERE condition
  24. MySQL – Using COUNT(*) in the WHERE clause
  25. How to format number with “.” as thousands separator and “,” as decimal separator?
  26. How to check if field is null or empty in MySQL?
  27. INSERT INTO Table from multiple tables
  28. Execute INSERT if table is empty?
  29. Mysql How do you create a clustered index?
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment