Count table rows

Question

What is the MySQL command to retrieve the count of records in a table

User · Answer

Just do a  SELECT COUNT    FROM table    You can specify conditions with a Where after that  SELECT COUNT    FROM table WHERE eye color  brown

User · Answer

sql  SELECT count    as toplam FROM wp postmeta WHERE meta key  ICERIK  AND post id    id   total   0   sqls   mysql query  sql  conn   if    sqls          total   mysql result  sqls  0      echo  Total    total

User · Answer

It can be convenient to select count with filter by indexed field  Try this EXPLAIN SELECT   FROM table name WHERE key  lt  anything

User · Answer

Because nobody mentioned it   show table status    lists all tables along with some additional information  including estimated rows for each table  This is what phpMyAdmin is using for its database page   This information is available in MySQL 4  probably in MySQL 3 23 too - long time prior information schema database   UPDATE   Because there was down-vote  I want to clarify that the number shown is estimated for InnoDB and TokuDB and it is absolutely correct for MyISAM and Aria  Maria  storage engines   Per the documentation      The number of rows  Some storage engines  such as MyISAM  store the   exact count  For other storage engines  such as InnoDB  this value is   an approximation  and may vary from the actual value by as much as 40    to 50   In such cases  use SELECT COUNT    to obtain an accurate   count    This also is fastest way to see the row count on MySQL  because query like   select count    from table    Doing full table scan what could be very expensive operation that might take hours on large high load server  It also increase disk I O   The same operation might block the table for inserts and updates - this happen only on exotic storage engines   InnoDB and TokuDB are OK with table lock  but need full table scan

User · Answer

SELECT COUNT    FROM fooTable    will count the number of rows in the table   See the reference manual

User · Answer

Simply   SELECT COUNT    FROM  tablename

User · Answer

If you have a primary key or a unique key index  the faster method possible  Tested with 4 millions row tables   SHOW INDEXES FROM  database tablename  WHERE Key Name   PRIMARY     and then get cardinality field  it is close to instant   Times where from 0 4s to 0 0001ms

User · Answer

You have to use count    returns the number of rows that matches a  specified criteria   select count    from table name

User · Answer

If you have several fields in your table and your table is huge  it s better DO NOT USE   because of it load all fields to memory and using the following will have better performance   SELECT COUNT 1  FROM fooTable

User · Answer

We have another way to find out the number of rows in a table without running a select query on that table   Every MySQL instance has information schema database  If you run the following query  it will give complete details about the table including the approximate number of rows in that table   select   from information schema TABLES where table name    table name  G

User · Answer

select count    from YourTable

User · Answer

As mentioned by Santosh  I think this query is suitably fast  while not querying all the table   To return integer result of number of data records  for a specific tablename in a particular database   select TABLE ROWS from information schema TABLES where TABLE SCHEMA    database   AND table name  tablename

[mysql] Count table rows

Examples related to mysql