zhekanax zhekanax - 26 days ago 7
MySQL Question

The best way to delete 5K rows from Innodb table with 30M rows

table:


  • foreign_id_1

  • foreign_id_2

  • integer

  • date1

  • date2

  • primary(foreign_id_1, foreign_id_2)



Query:
delete from table where (foreign_id_1 = ? or foreign_id_2 = ?) and date2 < ?


Without date query takes about 40 sec. That's too high :( With date much more longer..

The options are:


  • create
    another table and
    insert
    select
    , then
    rename

  • use limit and run query multiple times

  • split query to run for
    foreign_id_1
    then
    foreign_id_2

  • use select then delete by single row



Is there any faster way?




mysql> explain select * from compatibility where user_id = 193 or person_id = 193 \G
id: 1
select_type: SIMPLE
table: compatibility
type: index_merge
possible_keys: PRIMARY,compatibility_person_id_user_id
key: PRIMARY,compatibility_person_id_user_id
key_len: 4,4
ref: NULL
rows: 2
Extra: Using union(PRIMARY,compatibility_person_id_user_id); Using where
1 row in set (0.00 sec)

mysql> explain select * from compatibility where (user_id = 193 or person_id = 193) and updated_at < '2010-12-02 22:55:33' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: compatibility
type: index_merge
possible_keys: PRIMARY,compatibility_person_id_user_id
key: PRIMARY,compatibility_person_id_user_id
key_len: 4,4
ref: NULL
rows: 2
Extra: Using union(PRIMARY,compatibility_person_id_user_id); Using where
1 row in set (0.00 sec)

Answer Source

By now data amount is 40M (+33%) and rapidly growing. So I've started looking for other, some no-sql, solution.

Thanks.