Databases
StackOverflow2013
Postsdbo
POTrying to efficiently delete records in table which has multicolumn index
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrying to efficiently delete records in table which has multicolumn index
    primarykey
    data
    text
    <p>I am using MySQL 5.6 on Linux (RHEL). The database client is a Java program. The table in question (MyISAM or InnoDB, have tried both) has a multicolumn index comprising two integers (id's from other tables) and a timestamp. </p> <p>I want to delete records which have timestamps before a given date. I have found that this operation is relatively slow (on the order of 30 seconds in a table which has a few million records). But I've also found that if the other two fields in the index are specified, the operation is much faster. No big surprise there. </p> <p>I believe I could query the two non-timestamp tables for their index values and then loop over the delete operation, specifying one value of each id each time. I hope that wouldn't take too long; I haven't tried it yet. But it seems like I should be able to get MySQL to do the looping for me. I tried a query of the form</p> <pre><code>delete from mytable where timestamp &lt; '2013-08-17' and index1 in (select id from foo) and index2 in (select id from bar); </code></pre> <p>but that's actually slower than</p> <pre><code>delete from mytable where timestamp &lt; '2013-08-17'; </code></pre> <p>Two questions. (1) Is there something I can do to speed up delete operations which depend only on timestamp? (2) Failing that, is there something I can do to get MySQL to loop over two other two index columns (and do it quickly)?</p> <p>I actually tried this operation with both MyISAM and InnoDB tables with the same data -- they are approximately equally slow.</p> <p>Thanks in advance for any light you can shed on this problem.</p> <p>EDIT: More info about the table structure. Here is the output of <code>show create table mytable</code>:</p> <pre><code>CREATE TABLE `mytable` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `timestamp` datetime NOT NULL, `fooId` int(10) unsigned NOT NULL, `barId` int(10) unsigned NOT NULL, `baz` double DEFAULT NULL, `quux` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `fooId` (`fooId`,`barId`,`timestamp`) ) ENGINE=InnoDB AUTO_INCREMENT=14221944 DEFAULT CHARSET=latin1 COMMENT='stuff' </code></pre> <p>Here is the output of <code>show indexes from mytable</code>:</p> <pre><code>+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |mytable| 0 | PRIMARY | 1 | id | A | 2612681 | NULL | NULL | | BTREE | | | |mytable| 0 | fooId | 1 | fooId | A | 20 | NULL | NULL | | BTREE | | | |mytable| 0 | fooId | 2 | barId | A | 3294 | NULL | NULL | | BTREE | | | |mytable| 0 | fooId | 3 | timestamp | A | 2612681 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ </code></pre> <p>EDIT: More info -- output from "explain".</p> <pre><code>mysql&gt; explain delete from mytable using mytable inner join foo inner join bar where mytable.fooId=foo.id and mytable.barId=bar.id and timestamp&lt;'2012-08-27'; +----+-------------+-------+-------+---------------+---------+---------+-------------------------------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------------------------------+------+----------------------------------------------------+ | 1 | SIMPLE | foo | index | PRIMARY | name | 257 | NULL | 26 | Using index | | 1 | SIMPLE | bar | index | PRIMARY | name | 257 | NULL | 38 | Using index; Using join buffer (Block Nested Loop) | | 1 | SIMPLE |mytable| ref | fooId | fooId | 8 | foo.foo.id,foo.bar.id | 211 | Using where | +----+-------------+-------+-------+---------------+---------+---------+-------------------------------+------+----------------------------------------------------+ </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRobert Dodier
    1. USRobert Dodier
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COCan you also post some information about the table structure "show create table" command
      singulars
      1. POTrying to efficiently delete records in table which has multicolumn index
      1. USRaymond Nijland
    2. CO@RaymondNijland: I have added output from show create table and show indexes from. Thanks for the suggestion.
      singulars
      1. POTrying to efficiently delete records in table which has multicolumn index
      1. USRobert Dodier
    3. COAnd the tables foo and bar? But the problem is that you want to delete an large selection this means that your BTREE indexes need to be updated als well and this will slow down your deletes. i don't know how many records your selections matches maybe you can also run an count(*) note this can take about 5 or 10 minutes on an large innodb table.
      singulars
      1. POTrying to efficiently delete records in table which has multicolumn index
      1. USRaymond Nijland
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload