Does MySQL InnoDB table perform faster when data is removed

Question

I have a 5GB InnoDB table in MySQL. I updated several rows where a column had massive amounts of data to be blank, hoping to make the table smaller and perform quicker.

I then discovered that InnoDB doesn't reduce disk space when you remove data from it.

https://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table?rq=1

However, there is less data to search through now, so I'd like to assume it will still perform better, especially when performing a LIKE query.

I tried to do some benchmark tests, but very unfortunately did not take accurate before measurements.

Will my InnoDB table perform faster now that I've removed data from the table, or does it still have to search through the same amount of disk and there will be no difference.

Answer 1

InnoDB may not release the diskspace but your query on your table is actually hitting less data. The size of your actual table has been reduced, but InnoDB will keep the storage for later growth on that table (or any other table in that database).

Your InnoDB table may or may not perform better depending on what percentage of total rows you purged from your table. If you removed 5% of total rows, for example, you probably won't see noticable speedup (up to 5%). If you removed 90%, you'll see it speedup quite a bit.

Some other approaches to increasing performance is writing a better query or indexing the table such that lookups are faster.

Answer 2

Optimize Table to remove table fragmentation due to large volume of data correction may help to improve performance.