How to optimize a mysql query

Question

I have a problem with a mysql query that sometimes needs more than 80 secs to execute.

Could you please help me to optimize it?

Here my sql code

SELECT
 codFinAtl,
 nome,
 cognome,
 dataNascita AS annoNascita,
 MIN(tempoRisultato) AS tempoRisultato
FROM
graduatorie
INNER JOIN anagrafica ON codFin = codFinAtl
INNER JOIN manifestazionigrad ON manifestazionigrad.codice = graduatorie.codMan
WHERE
 anagrafica.eliminato = 'n' 
 AND graduatorie.eliminato = 'n' 
 AND codGara IN('01', '81') 
 AND sesso = 'F' 
 AND manifestazionigrad.aa = '2018/19' 
 AND graduatorie.baseVasca = '25' 
 AND tempoRisultato IS NOT NULL 
 AND dataNascita BETWEEN '20050101' AND '20061231'
GROUP BY
 codFinAtl
ORDER BY
 tempoRisultato,
 cognome,
 nome

And my db schema

[UPDATE]

Here there is the results of EXPLAIN query

+----+-------------+--------------------+------------+--------+--------------------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table              | partitions | type   | possible_keys            | key       | key_len | ref                           | rows   | filtered | Extra                                        |
+----+-------------+--------------------+------------+--------+--------------------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | anagrafica         | NULL       | ALL    | codFin                   | NULL      | NULL    | NULL                          | 334094 |     0.11 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | graduatorie        | NULL       | ref    | codMan,codFinAtl,codGara | codFinAtl | 33      | finsicilia.anagrafica.codFin  |     20 |     0.24 | Using where                                  |
|  1 | SIMPLE      | manifestazionigrad | NULL       | eq_ref | codice                   | codice    | 32      | finsicilia.graduatorie.codMan |      1 |    10.00 | Using index condition; Using where           |
+----+-------------+--------------------+------------+--------+--------------------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------+

In addition, questions about query optimisation always require the EXPLAIN for the given query — Strawberry, Mar 14 '19 at 21:35
And how come 'manifestazionigrad.codice' gets qualified, and nothing else? :-( ? — Strawberry, Mar 14 '19 at 21:38
We need to see the indexes; please provide `SHOW CREATE TABLE`. The query is clumsy to read since some columns are not qualified by the table they are in. Have you heard of "composite indexes"? — Rick James, Mar 14 '19 at 22:06

Rick James · Answer 1 · 2019-03-18T18:16:21.137

graduatorie:  INDEX(eliminato, baseVasca)
manifestazionigrad:  INDEX(aa, codMan)

These may not be sufficient. Please qualify each column in the query so we know which table they come from.

But the real problem is probably "explode-implode". First it explodes by JOINing, then it implodes by GROUP BY.

Is it possible to compute MIN(tempoRisultato) without any JOINs? (I can't even tell which table is involved.) If so, provide that, then we can discuss what to do next. There may be two choices: a derived table with the MIN, or a subquery in the JOIN that may be correlated or uncorrelated.

(tempoRisultato seems to be in two tables!)

How to optimize a mysql query

1 Answers1