It takes a lot of queries in mysql

Question

It takes a lot of queries in mysql

Navigation

#1 by (0 votes)

0

Hello friends I'm having a hard time finding what's wrong in my SELECT code in mysql, this is taking over 70sec I do not know why. Someone there to help me please?

Code:

SELECT id_aluno, nome_aluno, ano_lectivo, data data_pagamento_m, nome_classe, codigo_classe, nome_curso, valor_curso1, multa
FROM tb_matriculas
LEFT JOIN tb_classe ON tb_matriculas.classe = tb_classe.id_classe
LEFT JOIN tb_curso ON tb_curso.codigo_curso = tb_matriculas.curso
WHERE  data_pagamento_m < '$data_nova '
AND data_pagamento_m <> '2017-01-01'
AND YEAR(data_pagamento_m) = '$ano_select'
AND classe BETWEEN '$classe1'
AND '$classe2'
AND classe <> '10'
AND curso BETWEEN '$curso1' AND '$curso2'
AND turma BETWEEN $turma1 AND $turma2
AND periodo BETWEEN '$periodo1' AND '$periodo2'
GROUP BY id_aluno

sql query select

asked by anonymous 06.12.2017 / 17:02

1 answer

How to Limit the Submission of Forms to Users, Per Page? Google maps measure mileage between two points by the time

score 0 · Answer 1

If the table has many records and this query is very recurrent or important to return quickly, create an index containing all the fields used in the WHERE clause.

Review clauses with BETWEEN that are STRING that might compromise the query, such as the following:

AND classe BETWEEN '$classe1' AND '$classe2'
AND curso BETWEEN '$curso1' AND '$curso2'
AND periodo BETWEEN '$periodo1' AND '$periodo2'

The handling of strings works but if they have IDs it will be much faster to execute.

Change the LEFT JOIN to INNER JOIN as indicated by @Marconi, as the clauses are about students that must be in classes, courses and indicated periods it does not make sense to do a LEFT JOIN. This point will depend on your business rule.