How to optimize a mysql query

42
March 15, 2019, at 9:00 PM

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           |
+----+-------------+--------------------+------------+--------+--------------------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------+
READ ALSO
Angular 7 : Cannot add or update a child row: a foreign key constraint fails

Angular 7 : Cannot add or update a child row: a foreign key constraint fails

I am stuck on this SQL error and I do not understand why:

33
“Writing configuration file failed” in MySQL Server 8.0.15

“Writing configuration file failed” in MySQL Server 8.0.15

While set up the configuration in MySQL Server 80

20
Is there a standard connection dialog for mysql?

Is there a standard connection dialog for mysql?

I have written an windows form app in C# that gets distributed to various data partnersThe purpose of this app is to connect to one of their locally controlled databases, extract data according to SQL they provide and then to send it directly to our services...

24