mysql - 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 table (foreign_id_1 = ? or foreign_id_2 = ?) , date2 < ?

without date query takes 40 sec. that's high :( date more longer..

the options are:

• create table , insert select, rename
• use limit , run query multiple times
• split query run foreign_id_1 foreign_id_2
• use select delete single row

is there faster way?

---

mysql> explain select * compatibility 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 1 row in set (0.00 sec)  mysql> explain select * compatibility (user_id = 193 or person_id = 193) , 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 1 row in set (0.00 sec) 

having or in where makes mysql reluctant (if not refuse) use indexes on user_id and/or person_id fields (if there -- showing create table indicate if there was).

if can add indexes (or modify existing ones since i'm thinking of compound indexes), i'd add two:

alter table compatibility  add index user_id_updated_at (user_id, updated_at), add index persona_id_updated_at (person_id, updated_at); 

correspondingly, assuming rows delete didn't have be deleted atomically (i.e. occur @ same instant).

delete compatibility user_id = 193 , updated_at < '2010-12-02 22:55:33';  delete compatibility person_id = 193 , updated_at < '2010-12-02 22:55:33';