List:General Discussion« Previous MessageNext Message »
From:BAUMEISTER Alexandre Date:September 10 2001 2:17pm
Subject:InnoDB tables : difficulties with COUNT(*), DELETE etc ...
View as plain text  
Bonjour,

  I have a table created with :

CREATE TABLE mybuffer_NAS_D (
  id int(10) unsigned NOT NULL auto_increment,
  service varchar(10) NOT NULL default '',
  date datetime NOT NULL default '0000-00-00 00:00:00',
  data text,
  PRIMARY KEY  (id),
  KEY id_date (date)
) TYPE=InnoDB;  

  There  are  many  rows in it. And InnoDB take a VERY long time to do
  count(*) :(

mysql> select count(*) from mybuffer_NAS_D;
+----------+
| count(*) |
+----------+
|  4611891 |
+----------+
1 row in set (1 min 34.71 sec)

  If  I  select 100 first rows ordered by 'date asc', I see that there
  are less than 100 lines with date<'2001-08-16 00:00:00'.
  
mysql> select * from mybuffer_NAS_D order by date asc limit 100;
+----------+---------+---------------------+------------------------------------------------------------------+
| id       | service | date                | data                                         
                   |
+----------+---------+---------------------+------------------------------------------------------------------+
| 51155706 | live    | 2001-07-02 17:58:08 |
TPEEIX?019.95?10?20?30?40?612001/7/2?7117:58:08?J0?K0.00000      |
| 51196330 | live    | 2001-07-02 17:58:08 |
TPEEIX?019.95?10?20?30?40?612001/7/2?7117:58:08?J0?K0.00000      |
... cut ...
| 51192850 | live    | 2001-08-15 14:30:18 |
TISMT?015.19?10?20?30?40?612001/8/15?71?J5.19?K-5                |
| 51192851 | live    | 2001-08-15 14:30:18 |
TISMTW?010.74?10?20?30?40?612001/8/15?71?J0.74?K-5               |
| 51194111 | live    | 2001-08-15 14:30:20 |
TMAII?013.2?10?20?30?40?612001/8/15?71?J3.2?K-5                  |
| 51201163 | live    | 2001-08-15 14:30:33 |
TUSOLW?010.1?10?20?30?40?612001/8/15?71?J0.1?K0.00000            |
| 51201418 | live    | 2001-08-15 14:30:33 |
TVFND?010.11?10?20?30?40?612001/8/15?71?J0.11?K-5                |
| 51203036 | live    | 2001-08-15 14:30:37 |
TZSEV?015.52?10?20?30?40?612001/8/15?71?J5.52?K0.00000           |
| 51145454 | live    | 2001-08-16 02:00:22 |
TCNBA?0117.75?10?20?30?40?612001/8/16?71?J17.75?K0.00000         |
| 51152124 | live    | 2001-08-16 02:00:32 |
TMBLAP?0115.75?10?20?30?40?612001/8/16?71?J15.75?K0.00000        |
| 51154024 | live    | 2001-08-16 02:00:35 |
TNESC?011.35?10?20?30?40?612001/8/16?71?J1.35?K0.00000           |
| 51162377 | live    | 2001-08-16 02:00:48 |
TZYSCD?0120?10?20?30?40?612001/8/16?71?J20?K0.00000              |
| 51186600 | live    | 2001-08-16 14:30:08 |
TCNBA?0117.75?10?20?30?40?612001/8/16?71?J17.75?K0.00000         |
| 51194263 | live    | 2001-08-16 14:30:24 |
TMBLAP?0115.75?10?20?30?40?612001/8/16?71?J15.75?K0.00000        |
| 51203050 | live    | 2001-08-16 14:30:37 |
TZYSCD?0120?10?20?30?40?612001/8/16?71?J20?K0.00000              |
| 51143821 | live    | 2001-08-20 02:00:18 |
TADSTW?010.12?10?20?30?40?612001/8/20?71?J0.12?K0.00000          |
| 51157301 | live    | 2001-08-20 02:00:40 |
TRLCOW?010.16?10?20?30?40?612001/8/20?71?J0.16?K0.00000          |
| 51159119 | live    | 2001-08-20 02:00:42 |
TSSLI?013.2?10?20?30?40?612001/8/20?71?J3.2?K0.00000             |
+----------+---------+---------------------+------------------------------------------------------------------+
100 rows in set (0.42 sec)

  But  if  I  ask  Mysql  to delete these rows, it crashes after a few
  minutes :

mysql> delete from mybuffer_NAS_D where date<'2001-08-16 00:00:00';
ERROR 1030: Got error 1000000 from table handler

  I  think  I  already read a lot about these limitations. But this is
  really a problem for me now :(

  Any improvement expected ?

  Regards,
  Alex.

Thread
InnoDB tables : difficulties with COUNT(*), DELETE etc ...BAUMEISTER Alexandre10 Sep
Re: InnoDB tables : difficulties with COUNT(*), DELETE etc ...Heikki Tuuri10 Sep