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)

Answers


Having an OR in your WHERE makes MySQL reluctant (if not completely refuse) to use indexes on your user_id and/or person_id fields (if there is any -- showing the CREATE TABLE would indicate if there was).

If you can add indexes (or modify existing ones since I'm thinking of compound indexes), I'd likely 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 the rows to DELETE didn't have to be be deleted atomically (i.e. occur at the same instant).

DELETE FROM compatibility WHERE user_id = 193 AND updated_at < '2010-12-02 22:55:33';

DELETE FROM compatibility WHERE person_id = 193 AND updated_at < '2010-12-02 22:55:33';

Need Your Help

java byte array output stream gives nothing

java exec bytearrayoutputstream

I have the following code and I can't figure out why it won't work:

Raspberry Pi: Can I turn on a monitor over HDMI?

raspberry-pi monitor raspbian hdmi

I have a Model A Raspberry Pi running Raspbian. It is connected to a small monitor by HDMI (the monitor model: FEELWORLD FW819HAT).

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.