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.
---------------------------------------------------------------------
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
