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.
***
Christophe DIARRA
Institut de Physique Nucleaire
15, Rue Georges Clemenceau
Bat 102 - S2I
91406 ORSAY Cedex
Tel: (33) 1 69 15 65 60
Fax: (33) 1 69 15 45 03
(33) 1 69 15 64 70
E-mail: [EMAIL PROTECTED]
***
---------------------------------------------------------------------
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
