And Drupal already has a nasty reputation for running a boat-load of database queries. Is your website highly trafficked and dealing with huge amounts of data to be retrieved and displayed in real-time? This will put an extra strain on your MySQL database. So, how do you optimize your Drupal database?

Needless to add that this is the most straightforward ways to give your website a rendering performance boost:

The lighter and clutter-free your database tables are, the better your SQL query times will be.

And, the faster your web pages will load...

Now, when it comes to Drupal MySQL performance tuning, you're not running short of options. Here are the handiest and nonetheless effective techniques to consider:

 

1. OptimizeDB Module to Diagnose and Fix Tables Individually 

Let's face it: in Drupal 8 caching bloats up your database with “junk data” in no time.

Loads and loads of Drupal form cache get piled up in there and, frustratingly enough, it won't get cleared up even when you remove all cache. Or you run cron.

And this is where the OptimizeDB module comes in to save the day.

Meaning: to weight down your cache_form table by flushing the unnecessary stock of data.

It enables you to:

• empty your table right from your admin panel or by cron
• optimize your Drupal database tables and display its size
• set up notifications to alert you when performance issues get detected or whenever it's time for a regular maintenance check
• fix each of the identified issues

Now, here's a simulation of the module “in action:

To use it, go to /admin/config/development/optimizedb 

Next, you can either:

1. choose “Optimize tables” to execute a manual command on your database
2. or configure your alerts to get notified about the regular maintenance check-up and/or any tracked down errors

For more advanced operations of Drupal database optimization, explore the tools available in the next tab on that page: “List of tables in the database”.

There, you get to manually select the database that you want to execute your diagnostic and repair commands on:

• Just go to /admin/config/development/optimizedb/list_tables
• Choose your “target” databases and optimize them individually
• Have a look at the displayed size of all the tables in your database (a particularly useful piece of information that you can use to... take action)

Wrap up: you get to use the OptimizeDB module to check, diagnose, repair and schedule error & maintenance check-up notifications; you can also perform optimization operations on specific MySQL tables only.

 

2. DB Maintenance Module to Regularly Optimize Your Drupal Database  

Here's another strong ally when it comes to improving your Drupal MySQL performance. 

How is it different from OptimizeDB?

It enables you to diagnose and to automatically repair specific tables from your database during planned cron.php executions.

In short, once you've enabled it, you're free to run regular issue diagnosis, repair and maintenance scripts right from your Drupal 8 admin dashboard. No need to reach out to your web server for that.

Word of caution: do keep in mind that your MySQL tables get locked while the module executes its OPTIMIZE TABLE command.

And here's how you use it:

• Go to /admin/config/system/db_maintenance once you've installed your module
• Configure it to suit your database optimization needs: you might want to select the “Daily” option, then “Optimize all Tables” 
• Also, feel free to select the specific tables to be optimized

If you choose to select “Optimize now”, just wait for all the operations to be executed. 

 

3. Use a Solr Search Index as a Database Instead

Now, if you're dealing with particularly large volumes of data to be indexed, how about using Solr as your database?

For, in case of huge amounts of data, the Views module is the “usual suspect” behind the frustratingly low database queries. Therefore, consider shifting the heavy load onto an Apache Solr- based index instead.

Let it be the one storing and displaying data instead.

No more time-consuming queries to your overly exploited database.

Such a transfer would instantly weigh down and optimize your Drupal database. So, just ponder on this solution for a minute or two:

 

Using Solr as your database.

 

END of Part 1!

Stay tuned, for I have 2 more easy to implement and effective database optimization techniques to share with you...

---