List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:March 26 2007 9:01pm
Subject:Why doesn't the InnoDB count() match table status?
View as plain text  
Aside from the incredibly annoying fact that InnoDB tables don't store a
total COUNT(), my question is... Why are these numbers different? I could
easily parse out the second query which is REDICULOUSLY faster. BTW, why
doesn't mySQL just 'alias' the first query behind the scenes for us and
parse out the count?

mysql> SELECT COUNT(rsm_report_id) FROM Swordfish.rsm_reports;
+----------------------+
| COUNT(rsm_report_id) |
+----------------------+
|               277456 | 
+----------------------+
1 row in set (24.84 sec)

mysql> show table status where Name = 'rsm_reports'\G
*************************** 1. row ***************************
           Name: rsm_reports
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 296100  <<----- why doesn't this match above?!
 Avg_row_length: 3204
    Data_length: 948961280
Max_data_length: 0
   Index_length: 13664256
      Data_free: 0
 Auto_increment: 277457  <<----- this can't be counted on
    Create_time: 2007-03-26 20:23:14
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: InnoDB free: 5120 kB
1 row in set (0.27 sec)


Thread
Why doesn't the InnoDB count() match table status?Daevid Vincent26 Mar
  • Re: Why doesn't the InnoDB count() match table status?Dan Nelson27 Mar
    • RE: Why doesn't the InnoDB count() match table status?Daevid Vincent27 Mar
      • Re: Why doesn't the InnoDB count() match table status?Dan Nelson27 Mar
        • RE: Why doesn't the InnoDB count() match table status?Daevid Vincent27 Mar
          • RE: Why doesn't the InnoDB count() match table status?Wm Mussatto27 Mar
          • Re: Why doesn't the InnoDB count() match table status?Jeremy Cole27 Mar
      • Re: Why doesn't the InnoDB count() match table status?Daniel Kasak27 Mar
        • RE: Why doesn't the InnoDB count() match table status?Daevid Vincent27 Mar
        • Re: Why doesn't the InnoDB count() match table status?Maciej Dobrzanski27 Mar
          • RE: Why doesn't the InnoDB count() match table status?Tim Lucia27 Mar
            • Re: Why doesn't the InnoDB count() match table status?Jochem van Dieten27 Mar