MySQL Fastest way to count number of rows

Question

Which way to count a number of rows should be faster in MySQL   This   SELECT COUNT    FROM     WHERE       Or  the alternative   SELECT 1 FROM     WHERE         and then count the results with a built-in function  e g  in PHP mysql num rows     One would think that the first method should be faster  as this is clearly database territory and the database engine should be faster than anybody else when determining things like this internally

User · Answer

EXPLAIN SELECT id FROM      did the trick for me  and I could see the number of rows under rows column of the result

User · Answer

A count    statement with a where condition on the primary key returned the row count much faster for me avoiding full table scan   SELECT COUNT    FROM     WHERE  lt PRIMARY KEY gt  IS NOT NULL    This was much faster for me than  SELECT COUNT    FROM

User · Answer

I handled tables for the German Government with sometimes 60 million records    And we needed to know many times the total rows   So we database programmers decided that in every table is record one always the record in which the total record numbers is stored  We updated this number  depending on INSERT or DELETE rows   We tried all other ways  This is by far the fastest way

User · Answer

Perhaps you may want to consider doing a SELECT max Id  - min Id    1   This will only work if your Ids are sequential and rows are not deleted   It is however very fast

User · Answer

When you COUNT    it takes in count column indexes  so it will be the best result  Mysql with MyISAM engine actually stores row count  it doensn t count all rows each time you try to count all rows   based on primary key s column   Using PHP to count rows is not very smart  because you have to send data from mysql to php  Why do it when you can achieve the same on the mysql side   If the COUNT    is slow  you should run EXPLAIN on the query  and check if indexes are really used  and where should they be added     The following is not the fastest way  but there is a case  where COUNT    doesn t really fit - when you start grouping results  you can run into problem  where COUNT doesn t really count all rows   The solution is SQL CALC FOUND ROWS  This is usually used when you are selecting rows but still need to know the total row count  for example  for paging   When you select data rows  just append the SQL CALC FOUND ROWS keyword after SELECT   SELECT SQL CALC FOUND ROWS  needed fields or    FROM table LIMIT 20 OFFSET 0    After you have selected needed rows  you can get the count with this single query   SELECT FOUND ROWS      FOUND ROWS   has to be called immediately after the data selecting query     In conclusion  everything actually comes down to how many entries you have and what is in the WHERE statement  You should really pay attention on how indexes are being used  when there are lots of rows  tens of thousands  millions  and up

User · Answer

This is the best query able to get the fastest results  SELECT SQL CALC FOUND ROWS 1 FROM  orders   SELECT FOUND ROWS     In my benchmark test  0 448s  This query takes 4 835s SELECT SQL CALC FOUND ROWS   FROM  orders   SELECT FOUND ROWS      count   takes 25 675s SELECT count    FROM  orders

User · Answer

If you need to get the count of the entire result set you can take following approach   SELECT SQL CALC FOUND ROWS   FROM table name LIMIT 5  SELECT FOUND ROWS      This isn t normally faster than using COUNT albeit one might think the opposite is the case because it s doing the calculation internally and doesn t send the data back to the user thus the performance improvement is suspected   Doing these two queries is good for pagination for getting totals but not particularly for using WHERE clauses

User · Answer

After speaking with my team-mates  Ricardo told us that the faster way is   show table status like   lt TABLE NAME gt    G   But you have to remember that the result may not be exact   You can use it from command line too     mysqlshow --status  lt DATABASE gt   lt TABLE NAME gt    More information  http   dev mysql com doc refman 5 7 en show-table-status html  And you can find a complete discussion at mysqlperformanceblog

User · Answer

I ve always understood that the below will give me the fastest response times   SELECT COUNT 1  FROM     WHERE

User · Answer

I did some benchmarks to compare the execution time of COUNT    vs COUNT id   id is the primary key of the table - indexed    Number of trials      10   1000 queries  Results     COUNT    is faster 7   VIEW GRAPH  benchmarkgraph  My advice is to use   SELECT COUNT    FROM table

User · Answer

This query  which is similar to what bayuah posted  shows a nice summary of all tables count inside a database    simplified version of stored procedure by Ivan Cachicatari which I highly recommend    SELECT TABLE NAME AS  Table Name   TABLE ROWS AS  Rows  FROM information schema TABLES WHERE TABLES TABLE SCHEMA     YOURDBNAME   AND TABLES TABLE TYPE    BASE TABLE      Example     ----------------- ---------    Table Name        Rows       ----------------- ---------    some table          10278     other table           995

User · Answer

Try this   SELECT     table rows  Rows Count  FROM     information schema tables WHERE     table name  Table Name  AND     table schema  Database Name

User · Answer

Great question  great answers  Here s a quick way to echo the results if anyone is reading this page and missing that part    counter   mysql query  SELECT COUNT    AS id FROM table     num   mysql fetch array  counter    count    num  id    echo   count

[mysql] MySQL: Fastest way to count number of rows

Examples related to mysql

Examples related to performance