Page 1 of 1

How can I speed up this query?

Posted: Tue Aug 22, 2006 1:35 pm
by stevietee
Hi All:

I have 1 table into which I load data on a daily basis, assinging the current date and an item code to the table and the primary index. (ie index PRIMARY date, itemcode).

I want to be able to compare the information I loaded today, for instance, with the information I loaded yesterday and generate a brief output of records that have changed. Below is a sample of the PHP code im using to achieve this. The thing is it works, but it is VERY SLOW. There are obviously better ways of doing this so can anyone point me in the right direction to speed it up a little?

Code: Select all

<?
	$query="select * from TABLE1 where daDate = '2006-08-22'";
	$line=mysql_query($query)or die(mysql_error());

	while($row=mysql_fetch_array($line)){
		$query2="select * from TABLE1 where daDate = '2006-08-21' AND itemCode =".$row['itemCode'];
		$line2=mysql_query($query2)or die(mysql_error());
		while($row2=mysql_fetch_array($line2)){
			if ($row['value'] <> $row2['value']) {
 				echo '<tr align = "center">'."\n";
				echo "\t".'<td>'.$row2['Description'].'</td><td>'.$row2[value].$row[value].'</td>'."\n"; echo '</tr>'."\n";
 			}
		}
	}
?>

Posted: Tue Aug 22, 2006 2:00 pm
by pickle
FYI: using (but with double quotes instead of single) tags highlights the syntax of PHP code, making it much easier to read ;)

This might work:

Code: Select all

SELECT
   *
FROM
   TABLE1
WHERE
   doDate = '2006-08-22' OR
   doDate = '2006-08-21'
ORDER BY
   itemCode
You can then loop through each itemCode & compare the values from the two dates.

Posted: Tue Aug 22, 2006 3:28 pm
by Ollie Saunders
AND not OR surely

Posted: Tue Aug 22, 2006 3:32 pm
by pickle
Nope, definitely an OR.

He wants information from both days, hence the OR. Plus, it would be difficult for a single field to have two values ;)

Posted: Tue Aug 22, 2006 3:43 pm
by Ollie Saunders
OK sorry, I wasn't looking that carefully.

Posted: Tue Aug 22, 2006 4:09 pm
by stevietee
Excellent, thanks. That appears to have done the trick.