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
mysql>create 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 '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
