Link to home
Start Free TrialLog in
Avatar of Zack
ZackFlag 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

Query.sql


Thank you. 

Avatar of David Favor
David Favor
Flag of United States of America image

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
log_slow_admin_statements

log-queries-not-using-indexes

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.
ASKER CERTIFIED SOLUTION
Avatar of theGhost_k8
theGhost_k8
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of Zack

ASKER

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.