0

I was reading a post on Percona (https://www.percona.com/blog/2010/12/09/mysql-optimize-tables-innodb-stop/) that said dropping and adding an index on a innodb table was almost 20* faster than using the table optimize syntax. Is this also true for myisam tables?

I have a 50 million row table and am deleting about 40k rows a day from it (while also adding in 50k new rows).

  • Just wondering why you are still using MyISAM? It's now deprecated! Do you have a compelling reason not to convert your tables to InnoDB? – Vérace May 21 '18 at 16:54
  • @Vérace Went through about 10 iterations of creating the same table in innodb and every variation performed worse. – user3783243 May 21 '18 at 17:04
  • How much worse? – Vérace May 21 '18 at 17:07
  • Considerably. Some queries took +50 minutes in the innodb format, vs. 2 minutes in myisam. Percona said This causing heavy consumption for RAM bandwidth and CPU cycles. The table is smaller in myisam format and you can get faster results if large_table will be converted back to myisam. so we've kept in myisam. We have reduced the table by 40 million rows recently though so I might benchmark it again though. – user3783243 May 21 '18 at 17:13
  • Have you tried file_per_table? Just a thought... – Vérace May 21 '18 at 17:38
1

(Some of this is off-topic.)

Fast ALTER

  • The handling of ADD/DROP INDEX is different for MyISAM and InnoDB.
  • Oracle is focusing only on InnoDB.
  • InnoDB can allow writes while an index is being built -- by keeping track of what needs to be added to the index. It already has a "Change Buffer" to optimize such. MyISAM would have to invent a similar mechanism, and has no 'transaction' hooks to help.

Therefore, I conclude that all(?) benefits from ALTER TABLE ... INLINE/INPLACE are handled inside the InnoDB engine, leaving MyISAM out in the cold.

Long ago, one flavor of online ALTER TABLE, namely adding a item to an ENUM (with restrictions), was implemented. But that only required modifying the .frm file.

OPTIMIZE

In one situation in a hundred, OPTIMIZE TABLE is useful in MyISAM. The need for OPTIMIZE in InnoDB is much less frequent.

One example (applies to MyISAM only): The table has a lot of churn -- either from UPDATEs that change the lengths of rows or from DELETE+INSERT. MyISAM's INSERT gives preference to filling in holes in the .MYD, which can lead to individual rows scattered across multiple disk blocks. This slows down SELECT, even for 'point queries'.

Time Series

Are you are deleting "old" rows and adding "new" rows? If so,

  • Your MyISAM may need OPTIMIZE, as explained above. But, with InnoDB, OPTIMIZE would be mostly a waste.
  • DELETEing 40M rows locks a MyISAM table for a long time.
  • There are other techniques to deal with big deletes.

Partitioning for time series

  • PARTITIONing, even with MyISAM (before MySQL 8.0), is possible. It might let you turn the time consuming DELETE into an instantaneous DROP PARTITION. More
  • Using PARTITIONing would probably obviate the need for OPTIMIZE, even for MyISAM.
  • Caveat: Usually when adding partitioning, all the indexes need rethinking.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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