Avatar of Zack
Flag for Australia asked on

MySQL - Query Tune (Index)

Hi EE,

Calling on MySQL DBA's out there, I have a query and EXPLAIN statement (attached) that require some help in terms of performance tuning.  What I think is missing in an index could someone recommend what index or series of indexes I should apply against the DB to help improve the queries performance. 

Of course, any query tips would be very much welcome as well :)

Explain Statement.json


Thank you. 

MySQL ServerSQLDatabases

Avatar of undefined
Last Comment

8/22/2022 - Mon
David Favor

A simple way to debug this type of problem is to enable logging of missing indexes.

In your config file... enable various logging...

# Logging

log_error           = /var/log/mysql/mariadb-error.log
log_warnings        = 2

general_log_file    = /var/log/mysql/mariadb-general.log
general_log         = OFF

slow_query_log_file = /var/log/mysql/mariadb-slow.log
slow_query_log      = ON
long_query_time     = 10
log_slow_rate_limit = 1
log_slow_verbosity  = query_plan,innodb,explain


Open in new window

The query/explain you provided is very complex + it does appear there are several missing indexes.

Notice in your JSON, there are many occurrences of the pattern...

              "rows_examined_per_scan": 139926,
              "rows_produced_per_join": 15388,
              "filtered": "11.00",

Open in new window

Everywhere you see many rows "scanned" with a large number, this suggests where a potential index can be added to speed up searching.

Tip: Break your query into more simple queries for easier debugging.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Does the system where this is being run have phpmyadmin access?
you can look through that interface to see  if it provides recommendations to improve performance.

Note David's suggestions can be dynamically instituted using
set global variable_name=value;

Deals with setting the long/slow  query log location and enabling long query logging.

It might be easier to help address what you are trying to do, then trying to improve performance based on what you chose to do.

I.e. you built something and are asking how to improve its effectiveness based on where you chose to place it.

Thanks for the help guys, I'm chipping away at this query now.

 InnoDB won't like a VARCHAR primary key because of its clustered nature - Fixing this has lead to big performance gain.
Your help has saved me hundreds of hours of internet surfing.