I have a problem with a bigger table on mysql 4.0.16-log / debian linux
I played around with indexes, delete quick and such, but I just can't get it
to work.
The following table holds >35mio rows and has >5mio inserts/replaces per
day. to clean it up I want to delete all rows older than X days.
I would be very happy if somebody could help me on this. I'm stuck. I worked
with tables of that size with 3.23.49-log and didn't have problems, although
I must say that the amount of inserts is very high in this case.
The server is a 2.5ghz pentium4, 1.5gb RAM, SCSI-RAID-disks and such
hardware, so performance should not be a problem. what variables in mysql should I
modify, has anybody experience with that and can
help?
thanks!
Richard
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment
|
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| datatable | 0 | PRIMARY | 1 | ixno | A
| NULL | NULL | NULL | | BTREE | |
| datatable | 0 | PRIMARY | 2 | srcno | A
| NULL | NULL | NULL | | BTREE | |
| datatable | 0 | PRIMARY | 3 | acttime | A
| NULL | NULL | NULL | | BTREE | |
| datatable | 0 | PRIMARY | 4 | tino | A
| 35919333 | NULL | NULL | | BTREE | |
| datatable | 1 | dzeit | 1 | acttime | A
| 119333 | NULL | NULL | | BTREE |
|
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> explain
datatable;
+---------------+----------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default |
Extra
|
+---------------+----------------------+------+-----+---------------------+-------+
| ixno | int(11) unsigned | | PRI | 0 |
|
| srcno | smallint(6) unsigned | | PRI | 0 |
|
| acttime | datetime | | PRI | 0000-00-00 00:00:00 |
|
| tino | int(10) unsigned | | PRI | 0 |
|
| gl | double(10,4) | YES | | NULL |
|
| gl_volumen | int(11) | YES | | NULL |
|
| bi | double(10,4) | YES | | NULL |
|
| bi_volumen | int(11) | YES | | NULL |
|
+---------------+----------------------+------+-----+---------------------+-------+
8 rows in set (0.00 sec)
mysql> select count(*) from datatable where acttime < '2003-11-14
09:39:49';
+----------+
| count(*) |
+----------+
| 7194367 |
+----------+
1 row in set (3 min 22.15 sec)
mysql> select count(*) from datatable;
+----------+
| count(*) |
+----------+
| 36003669 |
+----------+
1 row in set (5.87 sec)
mysql> delete quick from datatable where acttime < '2003-11-14 09:39:49';
or
mysql> delete from datatable where acttime < '2003-11-14 09:39:49';
...takes forever. I killed it after 20 hours...
--
GMX Weihnachts-Special: Seychellen-Traumreise zu gewinnen!
Rentier entlaufen. Finden Sie Rudolph! Als Belohnung winken
tolle Preise. http://www.gmx.net/de/cgi/specialmail/
+++ GMX - die erste Adresse für Mail, Message, More! +++
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
