Speeding up my queries in PHP

Question

I'm working on trying to speed up a webpage I have created. I know the issue is that I have a query within a query. I feel like there has to be a quicker way to accomplish the same results, but I'm running out of ideas. (My first attempt at this took 45 seconds for the page to load, now I'm down to about 6)

What I'm trying to do is pull run rate information from tables. I need to pull the correct startup and end of run rates from the runrate table, but all I have to go off of initially is the workcenter ID.

I feel like if the tables were set up a little bit better then it probably would've have been so difficult, but it's what I inherited and as a result I'm a bit stuck. I need to pull a month worth of data from each workcenter (about 15) where there can be as many as 4-5 runs each day... Quite a bit of data to process.

Here's the PHP code:

$qtotalStartup = mysql_query("
    SELECT startup.recordID, startup.date, startup.time, runrate.rate AS temRate, runrate.formID  
        FROM jos_a_inproc_startup startup JOIN jos_a_runrate runrate ON startup.recordID = runrate.recordID 
        WHERE startup.workcenterId = $id AND runrate.rate > 0 AND runrate.formID = 1 AND startup.date > DATE_SUB(NOW(), INTERVAL 1 MONTH)") or die(mysql_error());

$totalStartCtr = mysql_num_rows($qtotalStartup);


if ($totalStartCtr > 0) {
while($rtotalStartup = mysql_fetch_assoc($qtotalStartup)) {

        $hours = 0;
        $goalRate = 0;
        $sumHrRR = 0;
        $startDate = 0;
        $startTime = 0;

        $startupNum = $rtotalStartup['recordID'];
        $goalRate = $rtotalStartup['temRate'];
        $startDate = $rtotalStartup['date'];
        $startTime = $rtotalStartup['time'];    
        $startTime = strtotime($startDate . ' ' . $startTime);


//now that we have all of the startup form info, we can move to the end of run information

//this query will retrieve the correct date, time, and ending run rate for us to use with our calculations.
$qtotalEOR = mysql_query("
    SELECT eor.recordID AS eorRec, eor.date, eor.time, eor.startupid, runrate1.rate AS tempRate, runrate1.formID 
        FROM jos_a_inproc_eor eor JOIN jos_a_runrate runrate1 ON eor.recordID = runrate1.recordID 
        WHERE eor.startupid = $startupNum AND runrate1.rate > 0 AND runrate1.formID = 3") or die(mysql_error());

$totalEORCtr = mysql_num_rows($qtotalEOR);

if ($totalEORCtr > 0) {
while($rtotalEOR = mysql_fetch_assoc($qtotalEOR)) {

//reset the accumulator to 0 so we don't get extra 'bad' data.
$sumHrRR = 0;
$newGoalRate = 0;
$lastestDate = 0;
$latestTime = 0;

            $eorNum = $rtotalEOR['eorRec'];
            $latestDate = $rtotalEOR['date'];
            $latestTime = $rtotalEOR['time'];
            $latestTime = strtotime($latestDate . ' ' . $latestTime);
            $sumHrRR= $rtotalEOR['tempRate'];

Any ideas would be greatly appreciated. I know it may be difficult to understand what I'm trying to get at without much more information, so let me know if you need to know anything else. Thanks.

*PSA:* The `mysql_*` functions are [deprecated in PHP 5.5](http://php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated). It is not recommended for writing new code as it will prevent you from upgrading in the future. Instead, use either [MySQLi](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php) and [be a better PHP Developer](http://jason.pureconcepts.net/2012/08/better-php-developer/). — Jason McCreary, Jul 29 '13 at 18:35
have you profiled your queries in MySQL directly using EXPLAIN statement ? Do you have the proper indices in place ? — Maximus2012, Jul 29 '13 at 18:37
I do not... I only been with this company the past couple months, and I inherited a lot of 'bad' practices. I've only been exposed for PHP for a little over a month so pardon the PHP mistakes please... I'm still very new to it. — wiscWeb, Jul 29 '13 at 18:42
most of the times, if the page-load is slow then its the database queries that are a problem. That seems to be the case here. You might want to isolate your query statements and use MySQL EXPLAIN in MySQL Workbench/PHPMyAdmin: http://stackoverflow.com/questions/7999833/mysql-explain-query-understanding — Maximus2012, Jul 29 '13 at 18:44
Your PHP seems to be fine (except mysql_ functions) but its the database that seems to be the problem here. — Maximus2012, Jul 29 '13 at 18:45
I don't completely understand your code, but it appears that you are looping through the results of $qtotalStartup and running $qtotalEOR inside that loop. If that's true, bad idea. It would be better to run the second query once, with a list of values from the first query. — Dan Bracuk, Jul 29 '13 at 19:05
Ya, almost anytime you find yourself running a second query based off of a first query result(especially in some kind of loop), youre usually better off combining the 2 queries. Its actually kind of suprising youre doing that since you said youre new to php and that usually the approach people stronger in php than sql take. — Rooster, Jul 29 '13 at 19:06
Rooster and Dan... My main question was how to do I get the results in one query? I need two different values from the same column (rate). One with the recordID = to the startupID and the 2nd one = to the eorID... — wiscWeb, Jul 29 '13 at 19:21
Also, this is my first job as a web developer... Still VERY new to all of this, but that's why I reach out for help when needed. But, Maximus thanks. Page loading pretty much instantly now. — wiscWeb, Jul 29 '13 at 19:22
It might be worthwhile to develop your query outside php. If you need two values from the same column, then "where field in (value1, value2)" is the approach you want. — Dan Bracuk, Jul 29 '13 at 19:31
@dougjore: You need to learn about SQL JOINs. Get a good intro book on SQL and start reading. — Andy Lester, Jul 29 '13 at 19:42
Thanks Dan. But Andy, that's not very helpful. I hope you really enjoyed stating that. Make you feel all warm and cozy inside? I stated I'm pretty new to the game. I don't expect to be a SQL expert from one 2 credit course. I know I have a lot to learn. — wiscWeb, Jul 29 '13 at 20:10

piotrekkr · Accepted Answer · 2013-07-29T20:27:06.687

Maby try using multiple JOINS like this one:

SELECT startup.recordID, startup.date, startup.time, 
runrate.rate AS temRate, runrate.formID  

-- stuff from second query
eor.recordID AS eorRec, eor.date AS eor_date, 
eor.time AS eor_time, eor.startupid AS eor_startupid, 
runrate1.rate AS eor_tempRate, 
runrate1.formID AS runrate1_formID

FROM jos_a_inproc_startup startup 
JOIN jos_a_runrate runrate ON startup.recordID = runrate.recordID 

-- second query LEFT JOIN
LEFT JOIN jos_a_inproc_eor eor 
    ON eor.startupid = startup.recordID
LEFT JOIN jos_a_runrate runrate1 
    ON eor.recordID = runrate1.recordID 
    AND runrate1.rate > 0 
    AND runrate1.formID = 3

WHERE startup.workcenterId = $id 
    AND runrate.rate > 0 
    AND runrate.formID = 1 
    AND startup.date > DATE_SUB(NOW(), INTERVAL 1 MONTH)

I don't know if I'm right but I think that you are also doing some aggregation work with results inside PHP. You could do it inside database using like sum() or avg() and GROUP BY. You will save some time when transfering smaller result set from database to server and time for looping and aggregating inside PHP. Also most of the time using JOIN is much faster than using queries in loop or even subqueries inside query.

You should also check if indexes are set on columns you search in. Also use EXPLAIN to check how query is executed.

A little late to reply, but thanks. Using EXPLAIN and getting the indexes in did the trick. But to be safe and try to be more server friendly I took your advice and used multiple joins. — wiscWeb, Aug 01 '13 at 20:28

score 0 · Answer 2 · answered Jul 29 '13 at 19:48

0

you can use Mem-Cache techniques to make it much faster ,and try to make your queries the simpler that u can .. dont retrieve values that you dont use in your scripts ..

answered Jul 29 '13 at 19:48

Leo Bali

309
1
8

score 0 · Answer 3 · answered Jul 29 '13 at 19:57

How many records are you typically dealing with as output? How big are the tables? Have you reviewed the indexes? Have you analyzed them recently (rebuilt them)?

Also, are you sending the data back to the browser using deflate? See:

http://httpd.apache.org/docs/2.2/mod/mod_deflate.html

score 0 · Answer 4 · edited May 23 '17 at 10:31

Well, you could try using multiple INNER JOINs (see) and have only one query instead of one query inside a query, which greatly impacts on performance. You could try something like this, and tweaking it a little:

SELECT
    startup.recordID AS startupRecordID,
    startup.date AS startupDate,
    startup.time AS startupTime,
    runrate.rate,
    runrate.formID,
    eor.recordID AS eorRecordID,
    eor.date AS eorDate,
    eor.time AS eorTime,
    eor.startupid AS eorStartupID
FROM jos_a_inproc_startup startup
    INNER JOIN jos_a_runrate runrate
    ON startup.recordID = runrate.recordID
    INNER JOIN jos_a_inproc_eor eor
    ON startup.recordID = eor.startupid
WHERE
    startup.workcenterId = $id
    AND runrate.rate > 0
    AND runrate.formID = 1
    AND startup.date > DATE_SUB(NOW(), INTERVAL 1 MONTH)

Speeding up my queries in PHP

4 Answers4