PrestaShop Performance Using MySQL Slow Query Log

When your PrestaShop store shows sluggish behavior—whether on the public-facing side (slow page loads, checkout delays) or in the back office (painfully slow product updates, combination generation, order processing)—the bottleneck is often database queries that take too long. Enabling and reviewing the MySQL slow query log pinpoints the exact SQL statements causing delays, so you can:

• Identify missing indexes or poorly written queries
• Spot third-party modules or hooks adding overhead
• Tune or refactor problematic queries

Pro tip: Storage speed matters. Always choose NVMe SSD hosting—its superior I/O performance drastically reduces query times compared to spinning disks or SATA SSDs. We specialize in hosting selection, migration, fully tuned PHP runtimes, and optimized MySQL configs, because many default mysql.cnf files have key-caching disabled, crippling performance.

Note: Most steps can be done via your hosting control panel—phpMyAdmin or File Manager. Use SSH only if you’re comfortable with the command line.

1. Prepare for Logging

• Choose a log file path
  e.g. /var/log/mysql/slow-query.log—make sure this directory exists and is writable by MySQL.
• Pick your threshold
  Decide what “slow” means. For example, to catch queries slower than 2 seconds:
  

  SET GLOBAL long_query_time = 2;

2. Enable Slow Query Logging

A. Via phpMyAdmin (Control Panel)

• Open phpMyAdmin, select your PrestaShop database, click the SQL tab.
• Run each command in turn:
  

  SET GLOBAL long_query_time = 2;
  SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
  SET GLOBAL slow_query_log = ON;

• In phpMyAdmin’s “Variables” view, filter for slow_query_log, slow_query_log_file, and long_query_time to confirm.

B. Via MySQL CLI (SSH, Advanced)

• ssh user@yourserver.com
• mysql -u root -p
• At the mysql> prompt, run:
  

  SET GLOBAL long_query_time = 2;
  SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
  SET GLOBAL slow_query_log = ON;

• Verify with:
  

  SHOW VARIABLES
    WHERE Variable_name IN
      ('slow_query_log','slow_query_log_file','long_query_time');

• EXIT; ← closes the MySQL prompt

3. Capture Slow Queries

• Trigger the actions that usually feel slow—browse your storefront, update products, process orders, generate combinations, etc.
• Via your hosting control panel’s File Manager, navigate to /var/log/mysql/slow-query.log and download or view it.
• Advanced (SSH):
  

  ssh user@yourserver.com
   tail -n 200 /var/log/mysql/slow-query.log

• Optional (mysqldumpslow):
  

  mysqldumpslow -a -s t -t 10 /var/log/mysql/slow-query.log

4. Disable Slow Query Logging

A. Via phpMyAdmin

• In phpMyAdmin, go to the SQL tab.
• Run:
  

  SET GLOBAL slow_query_log = OFF;
  SET GLOBAL long_query_time = 10;

• Confirm in “Variables” that slow_query_log is OFF and long_query_time is reset.

B. Via SSH

• SSH & login as before.
• Run:
  

  SET GLOBAL slow_query_log = OFF;
  SET GLOBAL long_query_time = 10;

• Verify with SHOW VARIABLES.
• EXIT; ← ends your MySQL session

5. Clean Up (Optional)

• Rotate or clear the log to free space:
  

  > /var/log/mysql/slow-query.log

• Add these settings into your my.cnf and configure log rotation for ongoing monitoring.

Remember: Always test changes on a staging or copy of your production site before applying them to live.