Alex,
slowness of count(*) is a well-known problem.
I will fix it some time this fall. Not very easy,
because of multiversioning and recovery.
The other problem you have is that a delete operation
ends up in a deadlock (I think it should not be called
a 'crash').
I will write in October a selective deadlock
resolution algorithm to InnoDB where a big
transaction is not chosen as the victim in a
deadlock situation.
Unfortunately EXPLAIN does not work on a DELETE,
and you cannot ask how MySQL does the delete.
You should download the version 3.23.42 and do
mysqlcreate table innodb_lock_monitor (a int) type = innodb;
so that we would see what locks and lock waits happen
during the delete. That could give us a clue how
to fix the delete inefficiency.
Regards,
Heikki
http://www.innodb.com
At 04:17 PM 9/10/01 +0200, you wrote:
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 '-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.0 |
| 51196330 | live| 2001-07-02 17:58:08 |
TPEEIX?019.95?10?20?30?40?612001/7/2?7117:58:08?J0?K0.0 |
... 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.0|
| 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.0 |
| 51145454 | live| 2001-08-16 02:00:22 |
TCNBA?0117.75?10?20?30?40?612001/8/16?71?J17.75?K0.0 |
| 51152124 | live| 2001-08-16 02:00:32 |
TMBLAP?0115.75?10?20?30?40?612001/8/16?71?J15.75?K0.0|
| 51154024 | live| 2001-08-16 02:00:35 |
TNESC?011.35?10?20?30?40?612001/8/16?71?J1.35?K0.0 |
| 51162377 | live| 2001-08-16 02:00:48 |
TZYSCD?0120?10?20?30?40?612001/8/16?71?J20?K0.0 |
| 51186600 | live| 2001-08-16 14:30:08 |
TCNBA?0117.75?10?20?30?40?612001/8/16?71?J17.75?K0.0 |
| 51194263 | live| 2001-08-16 14:30:24 |
TMBLAP?0115.75?10?20?30?40?612001/8/16?71?J15.75?K0.0|
| 51203050 | live| 2001-08-16 14:30:37 |
TZYSCD?0120?10?20?30?40?612001/8/16?71?J20?K0.0 |
| 51143821 | live| 2001-08-20 02:00:18 |
TADSTW?010.12?10?20?30?40?612001/8/20?71?J0.12?K0.0 |
| 51157301 | live| 2001-08-20 02:00:40 |
TRLCOW?010.16?10?20?30?40?612001/8/20?71?J0.16?K0.0 |
| 51159119 | live| 2001-08-20 02:00:42 |
TSSLI?013.2?10?20?30?40?612001/8/20?71?J3.2?K0.0 |
+--+-+-+---
---+
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 100 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
