Most efficient way to get table row count

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  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

User · Answer

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  gt  1 DB    at least for MySQL  instead

User · Answer

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   lt    tablename         tablename    next increment       0   qShowStatus           SHOW TABLE STATUS LIKE   tablename     qShowStatusResult    mysql query  qShowStatus  or die    Query failed      mysql error       lt br  gt      qShowStatus      row   mysql fetch assoc  qShowStatusResult    next increment    row  Auto increment     echo  next increment number    next increment      gt

User · Answer

Controller  SomeNameModel   getNextID  this- gt  table    MODEL  class SomeNameModel extends CI Model   private static  db   function   construct      parent    construct      self   db- gt   amp get instance  - gt db      function  getNextID  table     return self   db- gt query  SHOW TABLE STATUS LIKE     table      - gt row  - gt Auto increment         other stuff code

User · Answer

try this  Execute this SQL   SHOW TABLE STATUS LIKE   lt tablename gt     and fetch the value of the field Auto increment

User · Answer

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

User · Answer

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

User · Answer

next id   mysql fetch assoc mysql query  SELECT MAX id  FROM table      next id  MAX id        next auto incr id   hope it helpful

User · Answer

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

[mysql] Most efficient way to get table row count

Examples related to mysql

Examples related to sql

Examples related to count