Databases
StackOverflow2013
Postsdbo
POSpeeding up my queries in PHP
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSpeeding up my queries in PHP
    primarykey
    data
    text
    <p>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)</p> <p>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. </p> <p>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.</p> <p>Here's the PHP code:</p> <pre><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 &gt; 0 AND runrate.formID = 1 AND startup.date &gt; DATE_SUB(NOW(), INTERVAL 1 MONTH)") or die(mysql_error()); $totalStartCtr = mysql_num_rows($qtotalStartup); if ($totalStartCtr &gt; 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 &gt; 0 AND runrate1.formID = 3") or die(mysql_error()); $totalEORCtr = mysql_num_rows($qtotalEOR); if ($totalEORCtr &gt; 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']; </code></pre> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USwiscWeb
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CO*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/).
      singulars
      1. POSpeeding up my queries in PHP
      1. USJason McCreary
    2. COhave you profiled your queries in MySQL directly using EXPLAIN statement ? Do you have the proper indices in place ?
      singulars
      1. POSpeeding up my queries in PHP
      1. USMaximus2012
    3. COI 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.
      singulars
      1. POSpeeding up my queries in PHP
      1. USwiscWeb
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload