PHP mysqli_query is taking too long

Question

i have a problem here. this is my code

$res = mysqli_query($conn, "SELECT * FROM ( SELECT * FROM ( SELECT m.subnum, ma.cd 
FROM tr_mcerr AS m LEFT JOIN tr_mcerract AS ma ON m.subnum = ma.subnum 
order by ma.dtact DESC ) AS A GROUP BY A.subnum ) 
AS B WHERE B.cd = '01'") or die(mysqli_error($conn));

$totalRows_1 = mysqli_num_rows($res);

As you can see, i just want to get the number of rows. Now the problem is, this code alone take about around 2 seconds to complete. There are 5 more code like this but with different table and fields. so, just to get the page completely loaded would takes around 13 seconds to complete.

Is there another solution for this?
What are the reason that might causes the long execution time?

Thanks

Answer 1

This is your query:

SELECT *
FROM (SELECT *
      FROM (SELECT m.subnum, ma.cd 
            FROM tr_mcerr AS m LEFT JOIN
                 tr_mcerract AS ma
                 ON m.subnum = ma.subnum 
           order by ma.dtact DESC
          ) AS A
     GROUP BY A.subnum
    ) AS B
 WHERE B.cd = '01';

What a mess if you just want the number of rows! To many subqueries and that unnecessary ORDER BY.

Here is a simpler version:

SELECT COUNT(DISTINCT m.subnum)
FROM tr_mcerr m LEFT JOIN
     tr_mcerract ma
     ON m.subnum = ma.subnum 
WHERE b.cd = '01';

You might say: "Oh, this returns a different value." But, that is because your query is based on indeterminate results. You have SELECT * with a GROUP BY and somehow magically expect MySQL to figure out exactly which row you intend for the unaggregated columns. Even though the MySQL documentation is quite explicit about this point.