2

I’m using MySql 5.5.46 and have an InnoDB table with a Bit Column (named “ENABLED”). There is no index on this column. The table has 26 million rows, so understandably, the statement

DELETE FROM my_table WHERE ENABLED = 0;

takes a really long time. My question is, is there anything I can do (without upgrading MySQL, which is not an option at this time), to speed up the time it takes to run this query? My “innodb_buffer_pool_size” variable is set to the following:

show variables like 'innodb_buffer_pool_size';
+-------------------------+-------------+
| Variable_name           | Value       |
+-------------------------+-------------+
| innodb_buffer_pool_size | 11674845184 |
+-------------------------+-------------+
Dave
  • 15,639
  • 133
  • 442
  • 830
  • What do you mean by upgrading mysql (upgrade mysql or schema index creation, update?) If not upgrade, why don't you index the column with bitmap index. – Ed Baker Dec 03 '15 at 21:36
  • By "upgrade" I mean use a higher version of MySql (e.g. "6.1.0"). – Dave Dec 03 '15 at 22:01
  • I would create a bitmap index, 26 millions rows is still pretty hefty for MYSQL and will still take a little while, but should speed up a lot. – Ed Baker Dec 03 '15 at 22:04
  • THis feature isn't available thouhg in the version of MySQL that I use, though, right? At lesat, this fails -- "CREATE BITMAP INDEX ON my_table (ENABLED);". – Dave Dec 04 '15 at 15:08
  • Yeah you're right, doesn't look like BITMAP is available on 5.5. Alternatively you could try a BTREE, will take more space but will speed up your query still. https://dev.mysql.com/doc/refman/5.5/en/create-index.html – Ed Baker Dec 06 '15 at 20:52

1 Answers1

0

Do the DELETE in "chunks" of 1000, based on the PRIMARY KEY. See Delete Big. That article goes into details about efficient ways to chunk, and what to do about gaps in the PK.

(With that 11GB buffer_pool, I assume you have 16GB of RAM?)

In general, MySQL will do a table scan instead of using an index if the number of rows to be selected is more than about 20% of the total number of rows. Hence, almost never are "flag" fields worth indexing by themselves.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 1
    I recommend you all peruse Rick's articles. Good stuff on indexing and partitions – Drew Dec 09 '15 at 00:15