PHP PDO too slow on SELECT with some joins

Question

I'm having a performace problem with the execution of a select in PHP PDO.

Using a script available here at stackoverflow (Simplest way to profile a PHP script), I identified where the problem IS, but I have not found a solution.

My select that is the problem is:

SELECT REDACAO.ID_REDACAO AS ID_REDACAO, 
    DATE_FORMAT(REDACAO.DATA,'%d/%m/%Y') AS DATAE, 
    ALUNO.ID_ALUNO AS ID_ALUNO, 
    (SELECT IFNULL((DATEDIFF(DATE_ADD((SELECT MAX(DATA) FROM REDACAO WHERE ID_ALUNO = ALUNO.ID_ALUNO AND ID_REDACAO NOT IN (SELECT ID_REDACAO FROM CORRECAO)), INTERVAL 7 DAY), now())),NULL) as DATA FROM REDACAO LIMIT 1) AS ULTIMA, 
    ALUNO.NOME as ALUNO, 
    REDACAO.ID_TEMA AS ID_TEMA,
    TEMA.TITULO as TEMA,
    TEMA.MOTIVACIONAIS AS MOTIVACIONAIS, 
    REDACAO.TEXTO AS TEXTO, 
    REDACAO.ID_STATUS AS STATUS,
    B.NOTA as NOTA, 
    B.RCORRIGIDA AS CORRIGIDA,
    B.NOTA1,
    B.COMENTARIO1,
    B.NOTA2,
    B.COMENTARIO2,
    B.NOTA3,
    B.COMENTARIO3,
    B.NOTA4,
    B.COMENTARIO4,
    B.NOTA5,
    B.COMENTARIO5,
    B.COMENTARIO6,
    C.COMENTARIO AS COMENTARIO 
FROM REDACAO 
    LEFT OUTER JOIN (SELECT SUM(CORRECAO.C1+CORRECAO.C2+CORRECAO.C3+CORRECAO.C4+CORRECAO.C5) AS NOTA, RCORRIGIDA AS RCORRIGIDA, CORRECAO.C1 as NOTA1, CORRECAO.COM1 as COMENTARIO1, CORRECAO.C2 as NOTA2, CORRECAO.COM2 as COMENTARIO2, CORRECAO.C3 as NOTA3, CORRECAO.COM3 as COMENTARIO3, CORRECAO.C4 as NOTA4, CORRECAO.COM4 as COMENTARIO4, CORRECAO.C5 as NOTA5, CORRECAO.COM5 as COMENTARIO5, CORRECAO.COMGERAL AS COMENTARIO6, CORRECAO.ID_REDACAO FROM CORRECAO GROUP BY CORRECAO.ID_REDACAO) B 
    ON B.ID_REDACAO = REDACAO.ID_REDACAO 
    JOIN ALUNO ON ALUNO.ID_ALUNO = REDACAO.ID_ALUNO 
    JOIN TEMA ON TEMA.ID_TEMA = REDACAO.ID_TEMA 
    LEFT OUTER JOIN (SELECT (COUNT(COMENTARIO.ID_COMENTARIO)) AS COMENTARIO, COMENTARIO.ID_REDACAO FROM COMENTARIO GROUP BY COMENTARIO.ID_REDACAO) C 
    ON C.ID_REDACAO = REDACAO.ID_REDACAO 
WHERE REDACAO.ID_PROFESSOR = $CodProfessor 
    and REDACAO.ID_STATUS != 6 
ORDER BY (CASE WHEN REDACAO.ID_STATUS = 4 THEN 1 ELSE 0 END) DESC

I'm using (PDO :: FETCH_ASSOC) to get the data. Some columns respond in less than 1 second and others in more than 20 seconds.

Any idea what could be the problem and how to solve it?

if you haven't done it yet, add indexes to crucial columns, for example: `ALTER TABLE CORRECAO ADD INDEX ID_REDACAO` adds index to the column you are joining on — n-dru, Apr 08 '15 at 14:34
you need to post your table schema, the results of `show indexes from ` for each table in the query, and the results of `explain` on the query you are having problems with — pala_, Apr 08 '15 at 14:38
Your bottleneck is the database, not PDO. Like others mentioned before me, profile the query by adding `EXPLAIN` before `SELECT` and run the query in MySQL terminal. — N.B., Apr 08 '15 at 14:41
could you provide some of your php code? with some performance debug info to prove what is the code line that has the delay. as other people already commented here usually it must be the query call when php wait for mysql server response. if it is not that string - you MUST provide php code then :-) and somebody will help you, I am sure — Alex, Apr 08 '15 at 14:44
The query utilizes a series of nested subselects, Why would you expect this to perform well on all but the smallest tables? — Mike Brant, Apr 08 '15 at 14:45
this is not a php-pdo problem. this is a mysql query problem, and there you should focus. — Aris, Apr 08 '15 at 14:56
Its not PDO's fault that you make so many joins. The more joins, the slower the query. — developerwjk, Apr 08 '15 at 15:02

Aris · Answer 1 · 2015-04-09T04:17:03.173

2

Your query contains following that will slow it down:

many joins
many subselects
select without where
functions like COUNT, isnull, datediff, sum.(some of these may cancel an index)
case when
order by
group by

Depending on your indexes, on how the tables are joined, and on how big are the tables, this will eventually get very slower.

Try using 'explain' command, and simplify the query if possible.

explain output

a good video about explain

edited Apr 09 '15 at 04:17

answered Apr 08 '15 at 15:04

Aris

4,643
1
41
38

* `SELECT`s without `WHERE` (is missing from the list) – axiac Apr 08 '15 at 15:08
A `SELECT` without a `WHERE` is perfectly fine if you need all the rows. `CASE` in not a big burden. – Rick James Apr 08 '15 at 20:47

score 0 · Answer 2 · answered Apr 08 '15 at 20:45

PDO is not at fault; the query is complex. And there may be missing indexes.

Turn this into a LEFT JOIN (because IN (SELECT...) optimizes poorly.

AND  ID_REDACAO NOT IN (  SELECT  ID_REDACAO FROM  CORRECAO)

Upgrade to 5.6; it has some improvements.

You have two JOIN ( SELECT ... ). Before 5.6 that would be optimized terribly. Move one of them out into a temp table, to which you add a suitable index.

In one of the subqueries, GROUP BY CORRECAO.ID_REDACAO seems to be unnecessary.

These indexes (or PRIMARY KEYs) are needed:

CORRECAO: (ID_REDACAO)
REDACAO: (ID_REDACAO), (ID_PROFESSOR)
ALUNO: (ID_ALUNO)
TEMA: (ID_TEMA)
COMENTARIO: (ID_REDACAO, ID_COMENTARIO) ("compound index")

If those suggestions do not help enough, come back with SHOW CREATE TABLE for each table.

PHP PDO too slow on SELECT with some joins

2 Answers2