CODEWITHSUNDEEP
mysql
Shabarinath Volam
Shabarinath Volam

Reputation: 747

Why the difference in the rows count?

Hi I don't understand why my MYSQL is behaving strangely.

Here i want to know number of records present in the table site_details inorder to get this i have executed select count(*) from site_details; I have got the result as 

+----------+
| count(*) |
+----------+
|     2024 |
+----------+

And then to verify again i have executed mysql> SHOW TABLE STATUS FROM msp LIKE 'site_details'\G I have got result like

 *************************** 1. row ***************************
           Name: site_details
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1612
 Avg_row_length: 71
    Data_length: 114688
Max_data_length: 0
   Index_length: 81920
      Data_free: 7340032
 Auto_increment: NULL
    Create_time: 2012-10-01 08:05:09
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

Observe the count difference between the rows count for the two queries

and then without modifying my table i have again executed the same query mysql> SHOW TABLE STATUS FROM msp LIKE 'site_details'\G and strangely i have got the result like 

 *************************** 1. row ***************************
           Name: site_details
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1934
 Avg_row_length: 59
    Data_length: 114688
Max_data_length: 0
   Index_length: 81920
      Data_free: 7340032
 Auto_increment: NULL
    Create_time: 2012-10-01 08:05:09
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

Observe the Rows column for the above queries I have got results like 2024 1612 1934

Why this is happening??

Upvotes: 0

Views: 120

Answers (2)

davek
davek

Reputation: 22915

select count(*)...

gives you the actual number of rows now

select count(1)...

gives the number of rows where the first column is not null

The others are database-dictionary-based metrics which are not updated in real time but are dependent upon statistics etc.

See here for info on COUNT:

http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count

So if you want the exact number of rows - duplicates and all - right now (open transactions perhaps excepted, depending on the isaolation level), use COUNT(*).

Upvotes: 1

PyQL
PyQL

Reputation: 1830

From MySQL Docs

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.

So, it's a normal behavior for InnoDB databases

Upvotes: 1

Related Questions