You need to take Moodle Offline before you optimize, running mysqlcheck. Therefor, since Moodle is offline it does not affect performance. You should also run a backup before hand since Moodle uses innodb for the storage engine and it will actually drop the table then recreate it.
Regularly running mysqlcheck on your database will keep it running smooth and healthy. You do not necessarily need to run it every other week but it is best to run it after every update/upgrade since when doing an upgrade many changes are made to the database. Mysqlcheck will defrag/free space/rebuild and reorganize the index and tables.
"You can use
OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly."
You can also use the below scripts, they will give you an idea if your tables are fragmented, in comparison, some data is redundant and some has their own unique information.
MySQL Tuning Primer