List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 21 1999 6:59pm
Subject:mysql speedproblem on deleting records with version 3.22.25
View as plain text  
<cut>

mysql> show index from tab_a;
Mariano> +---------+------------+---------------+--------------+---------------+-----
Mariano> ------+-------------+----------+
Mariano> | Table   | Non_unique | Key_name      | Seq_in_index | Column_name   |
Mariano> Collation | Cardinality | Sub_part |
Mariano> +---------+------------+---------------+--------------+---------------+-----
Mariano> ------+-------------+----------+
Mariano> | tab_a    |          1 | artikelnummer |            1 | artikelnummer | A
Mariano> |        NULL |     NULL |
Mariano> | tab_a    |          1 | wgnummer      |            1 | wgnummer      | A
Mariano> |        NULL |     NULL |
Mariano> | tab_a    |          1 | benutzer         |            1 | benutzer
Mariano> | A         |        NULL |     NULL |
Mariano> +---------+------------+---------------+--------------+---------------+-----
Mariano> ------+-------------+----------+
Mariano> 3 rows in set (0.00 sec)


Mariano> deleting around 12000 records with "delete from tab_a where benutzer=123"
Mariano> will take over 3 minutes on a AMD400 128MB Ram, Seagate IDE HD, Linux 2.2

Mariano> any ideas how to speedup this request (I think it must be something on the
Mariano> table design)
Mariano> but I need the three indexes because a user have to search like this:

Mariano> select artikelnummer  from tab_a where wgnummer='Printer' and benutzer='123'

Mariano> and on the next query it is:

Mariano> select * from tab_a where artikelnummer='Alpha123' and benutzer='123'

Mariano> Mariano

Suggestions:

Increase the 'key_buffer' variable.

If you always combine artikelnumber with benutzer when you search, you 
should remove the key on artikelnumber and instead change the key
(benutzer) to (benutzer,artikelnumber).

Regards,
Monty
Thread
mysql speedproblem on deleting records with version 3.22.25Mariano Glas20 Sep
  • mysql speedproblem on deleting records with version 3.22.25Michael Widenius22 Sep