Optimize table how long

It's still going but the CPU and disk usage on the server are very low so I'm starting to wonder if it's actually doing anything. Although space was getting low, there is still about 10gigs left. It gobbled up about another gig or so when I first ran it. Thomas Cleveland. Improve this question. Can you explain what you mean by "slowing down"? These operations should still be pretty efficient under most circumstances even on large tables. Also, edit the question to show the definition of the table.

GordonLinoff I added the table definition. By slowing down I mean that a single insert went from around 20 ms to around ms, that is still the same, also after deleting a lot of rows.

It's defintly worth it after deleting a lot of rows, but unfortunatly there is not much you can do to speed it up. You could reduce the the disk access to speed it up or use an ssd. Also you should make sure you have enough space on your disk, since it's copying the entire table in a temp. Is this an Innodb table? What's the host memory usage like?

Add a comment. Active Oldest Votes. Improve this answer. For more information, see Section For information about using this statement with partitioned tables and table partitions, see Section InnoDB Details.

Other Considerations. For example. MYD or. FORCE , which rebuilds the table to update index statistics and free unused space in the clustered index. An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation.

During the prepare phase, metadata is updated and an intermediate table is created. During the commit phase, table metadata changes are committed. When the server is started with the --skip-new option. The table copy method is used instead.

InnoDB stores data using a page-allocation method and does not suffer from fragmentation in the same way that legacy storage engines such as MyISAM do. When considering whether or not to run optimize, consider the workload of transactions that your server is expected to process:.

Some level of fragmentation is expected. MariaDB No new tables are created and there is no need to copy data from old tables to new tables. Instead, this feature loads n pages determined by innodb-defragment-n-pages and tries to move records so that pages would be full of records and then frees pages that are fully empty after the operation.