I'm a man of my word, as you can see. So, here I am now, ready to share with you 2 more efficient and conveniently easy to implement techniques to optimize your Drupal database.

If in part 1 of this blog post on database query optimization in Drupal I've highlighted:

• the “module way” (or “ways”, since it's 2 Drupal modules that I've drawn your attention to) of addressing the issue of low database queries
• and the Solr-based index solution for weighing down your Drupal database (for high traffic does come with its own inconveniences)

… in this post we'll be tapping into:

• the tools for Drupal 8 database performance tuning available to you right in your phpMyAdmin
• “the custom PHP script way” of optimizing your tables

So, without any further ado, let us dig in:

 

4. Use phpMyAdmin's Toolbox to Identify and Troubleshoot Database Issues

If you're looking for a handier solution — that shouldn't imply installing a module or moving your loads of data to a new index — how about using the Drupal 8 database optimization the tools right at hand?

In the phpMyAdmin toolbox.

Just:

1. log yourself into phpMyAdmin
2. select your target database scanning through the MySQL installations listed in the left-side menu
3. then click the “Status” tab

It's there that you get to delve deep into a “sea of powerful real-time information” on the selected database's status quo:

• data transfers
• errors in performing certain operations
• connections

In short: you'll get to leverage a whole suite of MySQL analytics to optimize your Drupal database by just logging into your cPanel or accessing the web server.

 

5. Execute a PHP Script to Optimize Your Drupal Database

And the code in your php file would look something like this:

<?php
$db = mysql_connect('localhost','user','password');
if(!$db) echo "cannot connect to the database";
mysql_select_db('user_drpl1');
$result=mysql_query('OPTIMIZE TABLE accesslog,cache,comments,node,users,watchdog;');
echo mysql_error();
?>

(Source: SiteGround.com)

Just remember to replace “password”, “user” and “user_drpl1” with your own data)

What this script will do is optimize the tables comments, access log, node, cache, watchdog. 

Note: feel free to add or remove tables from the query.

Once you've written the custom code, save the file in your Drupal folder.

Assuming now that you will have named it... “database-optimization.php”, all there's left for you to do is to run it in your browser:

http://www.yourdomain.com/drupal/database-optimization.php

A blank, error-free page is the best sign that you will have efficiently optimized your tables.

Note: you can also set a cron job when using this technique to schedule repetitive diagnosis & repair tasks; the script will then run and optimize your Drupal database at regular intervals.

Word of caution: no need to “abuse” from the cron job, though; having your optimization script executed once a week should be more than enough.

 

Bottom Line: A “Magic” Formula for Keeping Your Database Optimized

A winning trio for keeping your database queries ideally optimized would include:

1. the DB Maintenance module
2. the OptimizeDB module
3. the phpMyAdmin tools available for MySQL diagnosis and repair

The END!

Quite curious now: which one(s) of these methods have you already tested out to optimize your Drupal database?

Has it lived up to your performance tuning expectations?

---