Our server has a DB that's over 600 GB - we are deleting older rows that are non-essential to contain the growth. We can't optimize the table due to possible downtown on the table but we are interested to know if queries will be faster with smaller data in the table despite the fact that the table size will not shrink at all.

So basically if the size remains the same, but we are able to delete a significant amount of rows, will our more intensive queries be any faster?


2 Answers 2


If you are worried about table locks caused by DDL operations, like ALTER TABLE... ENGINE=InnoDB (what optimize does for InnodB) you must know that in 5.6, that process can be done fully online, and alternatively, and for lower versions, you can use an online alter table tool like pt-online-schema-change. With proper care, the process can be done fully online and with minimal load increase (although you will need double the table size). Make sure also you are using innodb_file_per_table.

Assuming the table has been optimized and all rows purged, yes, you can obtain an improvement due to the several reasons:

  • Assuming you do not have 600GB of RAM, a larger part of your InnoDB pages will fit into the buffer pool, reducing IO requests. You can check that before and after by monitoring your physical IO or checking InnoDB buffer pool hit statistics, such as innodb_buffer_pool_read_requests and innodb_buffer_pool_reads
  • Having more free space on the buffer pool can also smooth the commit writing process by allowing more dirty pages on memory (this depends highly on the load)
  • If your queries require full table scans, reducing the total number of rows will make those operations faster
  • Index scans should also be faster, specially Index accesses and large ranges. Also InnoDB takes a huge impact when the hot parts of the indexes do not fit in memory (its trees are designed to be cached)
  • Temporary tables, sorts, etc. created could potentially be smaller, reducing the latency of its execution

So the actual improvement may depend a lot on the actual queries executed, usage or not of hashed searches, memory state, penalty of random IO reads, etc. But in general, working with smaller data sets is easier.

Beware also of the purge process, which can bite your performance if not done well.

Some of this advantages could be lower if there is a lot of fragmentation, but that depends a lot on the actual state of the table. You can see some thoughts about optimize on InnoDB here (more on comments):

  • 1
    thanks so much for the detailed answer. If I chose not to optimize and leave the table as is while still deleting unneeded rows, do you think there would be any improvement with index scans?
    – yoloman
    Feb 17, 2015 at 14:20
  • @yoloman Some of this advantages could be lower if there is a lot of fragmentation, but that depends a lot on the actual state of the table. In other words, try it- worst case scenario, no pages are removed and you may not get much advantage.
    – jynus
    Feb 17, 2015 at 18:07

The short answer: Queries will not be noticeably faster.

The long answer...

If you are doing table scans of 600GB, they needs fixed. And, yes, this type of query will be faster. But nothing else will be faster...

Deleting a bunch or rows will not help performance at all unless you were touching those to-be-deleted rows. If you were not touching them, they would not be in the buffer_pool. That is, after the DELETE, your "working set" has not changed, nor have many of the rows/blocks moved to any better place. So all activity will continue at the same speed.

All you gain by the DELETE is a lot of free blocks that the Operating System cannot see. Instead, they are hiding in ibdata1 or the .ibd file.

I doubt seriously if there is any 'online' option in 5.6 or 5.7 that will free up the empty space and compact the table without doing a full copy and replace. This would cause the table to be locked for a long time. It would not be for 600GB's worth of time, but rather however much is still active.

pt-online-schema-change much make a copy of the table in a piecemeal way so as to not lock for long, and it must use a trigger. Now the value of innodb_file_per_table becomes critical, else you might run out of disk space and/or not recoup the freed space. I strongly recommend =1 before building big tables like this one. If your table is in ibdata1 (CREATEd with =0), you are out of luck at this late date.

Fragmentation in InnoDB is rarely a performance problem, even after a massive delete.

If this purge is based on time, and will be repeated, then you really should have used PARTITION BY RANGE(date). I cover that in detail (including code) in http://mysql.rjweb.org/doc.php/partitionmaint . But, again, you cannot add PARTITIONs without locking and copying. Maybe pt-online-schema-change will do it, but there could be a limitation.

  • excellent answer, thanks for the details!
    – yoloman
    Feb 18, 2015 at 10:03
  • @Rick James ALTER TABLE ... ENGINE=InnoDB can be done online, no locks, since 5.6.17, although it will be as costly as a table copy: dev.mysql.com/doc/refman/5.6/en/… I also do not agree with "Deleting a bunch or rows will not help performance at all unless you were touching those to-be-deleted rows."- Index tree depth and row versions, if not purged, will be affected by the total number of rows, even if some rows are not used at all. It is true, however, that "Fragmentation in InnoDB is rarely a performance problem".
    – jynus
    Feb 19, 2015 at 13:47
  • A million rows has a BTree depth of about 3; a trillion rows, 6. You would have to delete a lot of rows to shrink the BTree depth by 1 -- namely about 99% of the rows. And the BTree depth is not much of a factor, since the non-leaf nodes tend to stay cache.
    – Rick James
    Feb 19, 2015 at 23:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.