Dear Christophe, > Type: InnoDB > Rows: 603 <<<<<<<<<<<<<< 700 != 603 >>>>>>>>>>>>>> > How do you explain the difference between "count(*): 700" and "Rows: 603"
You don't have to explain this, the manual does ;-) http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#U sing_InnoDB_tables "Note that the statistics SHOW gives about InnoDB tables are only approximate: they are used in SQL optimisation. Table and index reserved sizes in bytes are accurate, though." HTH! -- Stefan Hinz <[EMAIL PROTECTED]> CEO / Geschäftsleitung iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 ----- Original Message ----- From: "Christophe DIARRA" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, December 13, 2002 9:38 PM Subject: Fastest way to get the number of rows in a table ? > Hi. > > I need to know the number of rows in a table. > > "select count(*) from a_table" works fine but it is slow when the table > is big. > > I am trying instead "show table status like 'a_table'". The column 'Rows' > should give me the number of rows in the table 'a_table'. This works fine > for most of the tables but with one table, the value displayed is wrong. > > Demonstration (the database is quiet) : > > mysql> select count(*) from photo\G > *************************** 1. row *************************** > count(*): 700 > 1 row in set (0.00 sec) > > mysql> show table status like 'photo'\G > *************************** 1. row *************************** > Name: photo > Type: InnoDB > Row_format: Dynamic > Rows: 603 <<<<<<<<<<<<<< 700 != 603 >>>>>>>>>>>>>> > Avg_row_length: 135 > Data_length: 81920 > Max_data_length: NULL > Index_length: 98304 > Data_free: 0 > Auto_increment: 19314 > Create_time: NULL > Update_time: NULL > Check_time: NULL > Create_options: > Comment: InnoDB free: 2940928 kB; (iddatephoto) REFER > dbcourant/datephoto(iddatephoto) ON DELETE CASCADE > 1 row in set (0.01 sec) > > How do you explain the difference between "count(*): 700" and "Rows: 603" > ? > > About the table photo: > > mysql> show create table photo\G > *************************** 1. row *************************** > Table: photo > Create Table: CREATE TABLE `photo` ( > `idphoto` bigint(20) NOT NULL auto_increment, > `idpropriete` int(11) NOT NULL default '0', > `iddatephoto` bigint(20) NOT NULL default '0', > `valeur` double default NULL, > `prefixe` varchar(16) default NULL, > `min` double default NULL, > `max` double default NULL, > PRIMARY KEY (`idphoto`), > UNIQUE KEY `idpropriete` (`idpropriete`,`iddatephoto`), > KEY `iddatephoto` (`iddatephoto`), > FOREIGN KEY (`iddatephoto`) REFERENCES `dbcourant.datephoto` > (`iddatephoto`) ON DELETE CASCADE > ) TYPE=InnoDB > 1 row in set (0.01 sec) > > mysql> show create table datephoto\G > *************************** 1. row *************************** > Table: datephoto > Create Table: CREATE TABLE `datephoto` ( > `iddatephoto` bigint(20) NOT NULL auto_increment, > `datephoto` datetime NOT NULL default '0000-00-00 00:00:00', > `idarchive` bigint(20) NOT NULL default '0', > `jamaiseffacer` char(1) NOT NULL default '', > PRIMARY KEY (`iddatephoto`) > ) TYPE=InnoDB > 1 row in set (0.00 sec) > > Thanks in advance for your help. > > Christophe. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
