Moodle Tip - Optimize Your MySQL Tables

Problem - How can I optimize Moodle's MySQL database so that it will work better, faster? I didn't know it, but every table in a MySQL database can build up something called "overhead." Overhead is caused by rows being deleted, leaving empty space. I've heard that if I "optimize" MySQL tables, overhead will go away, but what does it all mean and how do I do that?

Solution - Use PHPMyAdmin to optimize your Moodle database tables. Over time, databases require maintenance to ensure optimal performance. This can be accomplished by optimizing the database tables.

Optimization involves removing all deleted rows, compressing, reordering, working with indexes, and more.

Since I didn't know what to do about optimizing MySQL databases, I asked my personal learning network via Twitter, and Jeffery Watkins (Managing Partner,, Moodle Partner) wrote me back with a short walk through (which appears below):
On the main screen, the one which lists all of the tables. Scroll to the bottom and select all of the tables. Then, in the drop down that says "With Selected" choose Optimize Database.

You should do this about once a month or even more if the site is getting heavy use.
Of course, as soon as Jeffery explained it, I was able to follow up reading these two tutorials on the subject. Doing the optimization is pretty easy, though, and I've gotten into the habit.

Here are the steps I followed:

  1. Installed PHPMyAdmin on the server.
  2. Check out my list of databases, which looks like this (partial list):
  3. Notice that the last column above is entitled OVERHEAD. To optimize the dbase, I click on "ctrcenter_moodle" and PHPMyAdmin gives me a list of the tables in that database. There are about 253 tables in that Moodle database. Only a few of them have overhead. I scroll to the bottom of the page and select the option that says "CHECK TABLES HAVING OVERHEAD"

    Notice that the last column "Overhead" has 532.3 KB. That's how much overhead I have total. I want to eliminate that overhead by clicking on WITH SELECTED dropdown and choosing OPTIMIZE TABLE, as shown in the image below:
  4. After selecting OPTIMIZE TABLE, I see that the SQL Optimize command is run:
  5. When I go back to look at my table results to see if the overhead is gone, I see that the OPTIMIZE option has been successful:

And, that's pretty much the process! If there's a quicker way (this was pretty quick) maybe to do all dbases at once, please share!

Be sure to read the following Moodle articles...
  1. Moodle Tips Roundup - a list of tips I've picked up in my Moodle journey.
  2. Doing the Moodle Mambo
  3. Moodle Habitudes: Constructing Online Learning Environments
  4. Book Review - Moodle 1.9: E-Learning Course Development by William Rice
  5. Why Moodle? A few reasons culled from around the Web using Google
  6. Moodle-izing Education (due for publication in Education World...find it here in mid-March, 2009)
  7. Moodle Questions during My Guest Spot on Classroom 2.0 LIVE
  8. Moodle in the Classroom - My Notes from Tomaz Lasic's Preso on Classroom 2.0 LIVE
  9. How to Manage Your District's Online Learning Opportunities (Education World)
  10. 5 Essential Technology Tools for Administrators (Education World)
  11. Why Moodle? A few reasons culled from around the Web using Google
  12. Moodle-izing Your Education Enterprise (Education World)

Subscribe to Around the

Be sure to visit the ShareMore! Wiki.

Everything posted on Miguel Guhlin's blogs/wikis are his personal opinion and do not necessarily represent the views of his employer(s) or its clients. Read Full Disclosure


Popular posts from this blog

COVID-19 Droplet Spread and #FREE Tests

Trying a New Pup Out #SPCA #Dog

AudibleNotes: Culturally Responsive Teaching for Multilingual Learners