It takes a long time to query in mysql

Asked

Viewed 920 times

0

Hello friends I’m having a hard time finding what is wrong in my selection code (SELECT) in mysql, this taking more than 70sec 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
  • 1

    create indexes for your table, which will result in more speed in your search

  • 1

    Why GROUP BY id_aluno?

  • Another question, are you sure it is LEFT JOIN, recommend reading: What is the difference between INNER JOIN and OUTER JOIN?

  • Hello thanks, I have created a indece in the table tb_matriculas, the GROUP BY id_student to group per student

  • Run a EXPLAIN of your query and analyze the result.

  • Function YEAR breaks an index if there is one in the data_payment_m column , and seems to have a model problem because payment is an attribute of which table ? There is only one payment ?

  • You use the GROUP BY clause but do not use any aggregation function in your selection list. What you want to group?

Show 2 more comments

1 answer

0

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 BETWEEN clauses that receive STRING that can 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'

String handling works but if you have Ids it will be much faster to run.

Change the LEFT JOIN to INNER JOIN as per @Marconi, as the clauses are about students who must be in classes, courses and periods indicated it makes no sense to make a LEFT JOIN. This point will depend on your business rule.

Browser other questions tagged sql query select

You are not signed in. Login or sign up in order to post.