Improve a query

[skeetley]

Hi,
I have this query:

$sql = 'ALTER TABLE `Actual_Hourly_2004` ADD INDEX ( `Date_Valid` ) ';
$result = mysql_query ('SELECT `FMFP_ID` , `Date_Valid` , `Time_Valid` , `Temp` , `RH` , `CDir` , `WSpd` , `WGst` , `Rn_1`  FROM Actual_Hourly_2004 ORDER BY `Date_Valid` asc, `Time_Valid` asc');

It is taking too long and the database is still quite small. How can I quicken it up? All I really want in this line is the most recent record for each FMFP_ID, whatever that may be. This is why I order by the Date_Valid field, then the Time_Valid field.

[dotbob]

Firstly, where are you using this:

$sql = 'ALTER TABLE Actual_Hourly_2004 ADD INDEX (
Date_Valid ) ';

Secondly, cut down the SQL:

$result = mysql_query('SELECT * FROM Actual_Hourly_2004 ORDER BY Date_Valid, Time_Valid ASC');

[skeetley]

dotbob,
Thanks for the reply. I made that change to my query.

I am not sure how to use that add index command. That is the only place I use it. I thought it worked liked that. Guess I need to do some reading. Any more ideas to speed my query up?

[dotbob]

The ADD INDEX command isn't being queried against your DB as far as I can tell.

You generally use this when first setting up the table schema.

Don't use it every time you query the DB as it's unnecessary and will slow the query execution down.

Is the query still slow? If so, how slow are we talking?

How many records do you have in the DB?

[skeetley]

The query is still slow. My page takes about a second to load without the following. With that in there it takes nearly 19 seconds. According to phpMyAdmin Showing rows 0 - 29 (73242 total, Query took 0.0018 sec) Each hour for now and until the end of the calendar year, 100 records are added. For a total of about 876,000 at Decmeber 31.
It must be all these operations that I perform on the data slowing it down. This is why I wish there was a way to just grab the most recent record for each FMFP_ID and do those calculations on it.

if (!$link = mysql_connect('localhost', 'uuuu', 'ppppp'))
    exit("Unable to make a connection to the database");
if (!mysql_select_db("ops_db", $link))
    exit("Unable to select the database"); 

$array = array (); 
$result = mysql_query('SELECT * FROM Actual_Hourly_2004 ORDER BY Date_Valid, Time_Valid ASC');
while ($row = mysql_fetch_assoc ($result)) 
{ 
    extract ($row); 
    $array[$FMFP_ID] = array ('Temp' => $Temp, 'RH' => $RH, 'Time_Valid' => $Time_Valid, 'Date_Valid' => $Date_Valid, 'CDir' => $CDir, 'WSpd' => $WSpd, 'WGst' => $WGst, 'Rn_1' => $Rn_1); 
	$array[$FMFP_ID]['Time_Valid']= 	ereg_replace(":00:00",":00 CST",$array[$FMFP_ID]['Time_Valid']);
	if ($array[$FMFP_ID]['Temp'] == -99.9){$array[$FMFP_ID]['Temp'] = "N/A";}
	else {$array[$FMFP_ID]['Temp'] = $array[$FMFP_ID]['Temp']."°C";}
	if ($array[$FMFP_ID]['RH'] == -99.9){$array[$FMFP_ID]['RH'] = "N/A";}
	else {$array[$FMFP_ID]['RH'] = round($array[$FMFP_ID]['RH'])."%";}
	if ($array[$FMFP_ID]['CDir'] == "MMM"){$array[$FMFP_ID]['CDir'] = "N/A";}
	if ($array[$FMFP_ID]['WSpd'] == -99.9){$array[$FMFP_ID]['CDir'] = "N/A";}
	if (($array[$FMFP_ID]['CDir'] != "N/A") && ($array[$FMFP_ID]['WSpd'] != -99.9) && ($array[$FMFP_ID]['WGst'] == -99.9)){$array[$FMFP_ID]['CDir'] = $array[$FMFP_ID]['CDir']." ".round($array[$FMFP_ID]['WSpd'])." km/h";}
	if (($array[$FMFP_ID]['CDir'] != "N/A") && ($array[$FMFP_ID]['WSpd'] != -99.9) && ($array[$FMFP_ID]['WGst'] != -99.9)){$array[$FMFP_ID]['CDir'] = $array[$FMFP_ID]['CDir']." ".round($array[$FMFP_ID]['WSpd'])." G".round($array[$FMFP_ID]['WGst'])." km/h";}
	if ($array[$FMFP_ID]['Rn_1'] == 0.0){$array[$FMFP_ID]['Rn_1'] = "Not reported";}
	if ($array[$FMFP_ID]['Rn_1'] != 0.0){$array[$FMFP_ID]['Rn_1'] = $array[$FMFP_ID]['Rn_1']."mm in the past hour";}
	$array[$FMFP_ID]['Date_Valid']=substr($array[$FMFP_ID]['Date_Valid'], 4);
	$array[$FMFP_ID]['Date_Valid']=ereg_replace("-01-","Jan-",$array[$FMFP_ID]['Date_Valid']);
	$array[$FMFP_ID]['Date_Valid']=ereg_replace("-02-","Feb-",$array[$FMFP_ID]['Date_Valid']);
	$array[$FMFP_ID]['Date_Valid']=ereg_replace("-03-","Mar-",$array[$FMFP_ID]['Date_Valid']);
	$array[$FMFP_ID]['Date_Valid']=ereg_replace("-04-","Apr-",$array[$FMFP_ID]['Date_Valid']);
	$array[$FMFP_ID]['Date_Valid']=ereg_replace("-05-","May-",$array[$FMFP_ID]['Date_Valid']);
	$array[$FMFP_ID]['Date_Valid']=ereg_replace("-06-","Jun-",$array[$FMFP_ID]['Date_Valid']);
	$array[$FMFP_ID]['Date_Valid']=ereg_replace("-07-","Jul-",$array[$FMFP_ID]['Date_Valid']);
	$array[$FMFP_ID]['Date_Valid']=ereg_replace("-08-","Aug-",$array[$FMFP_ID]['Date_Valid']);
	$array[$FMFP_ID]['Date_Valid']=ereg_replace("-09-","Sep-",$array[$FMFP_ID]['Date_Valid']);
	$array[$FMFP_ID]['Date_Valid']=ereg_replace("-10-","Oct-",$array[$FMFP_ID]['Date_Valid']);
	$array[$FMFP_ID]['Date_Valid']=ereg_replace("-11-","Nov-",$array[$FMFP_ID]['Date_Valid']);
	$array[$FMFP_ID]['Date_Valid']=ereg_replace("-12-","Dec-",$array[$FMFP_ID]['Date_Valid']);	
}

[dotbob]

Wow.

Took me five mins just to get the jist of that 😉

From what I can make out, you have multiple records for each FMFP_ID in the DB but only want to return the most recent record for each FMFP_ID (sorry if that's wrong).

In this case, substitute your query for this:

SELECT DISTINCT FMFP_ID, Date_Valid, Time_Valid, Temp, RH, CDir, WSpd, WGst, Rn_1 FROM Actual_Hourly_2004 ORDER BY Date_Valid, Time_Valid ASC'

This should return a much much much shorter recordset which should be pretty quick and should return the most recent record for each FMFP_ID

Right, am off home now. If you have any probs by all means reply and I'll sort it tomorrow morning for you (if i can).

Cheers

[skeetley]

dotbob,

Sorry, it is tough to understand, I am not a good PHPer yet.

What we have here is a database of weather stations. Each station has a name (FMFP_ID) and it reports hourly weather observations.

All that data goes into the db, many with -99.9 as the default. In order to format that, I add a degreeC or % or something onto the variables. I also round some variables.

The date and time are unfortunately stored seperately and like yyyy-mm-dd and ttt:mm:ss. SO what i do there is trim off the year, and change the month number to a 3 character string for the user. I also strip off :ss and add a timezone (CST).

The purpose of this script is that I just want to show the latest observations from each station. Due to factors beyond my control, sometimes a station won't report for a given hour or even a long period of time if it is vandalized or something.

With that latest select distinct query you gave me, the actually query is very fast. Once my while loop gets in there it adds about 13 seconds on to it still though. Maybe I could somehow embedd that while inside another while. Maybe while $result if FMFP_ID=FMFP_ID, skip the loop, else do the loop. That way the loop would only get done once for each FMFP_ID?? No idea, just brain storming.

[swr]

I suspect the "select distinct" isn't quite what you
want; the records themselves are distinct, even if
FMFP_ID isn't. I'm not sure if "group by" or
"having" will do what you want either. What really
should happen, is that the SQL query should
only return the results you want. That is, one
record per station.

I'd probably add another field, called "current".
Then when you insert a new record:

update Actual_Hourly_2004 set current = 0 where FMFP_ID = $station_id and current = 1
insert into Actual_Hourly_2004 .... (like you do now, except set current to 1)

Then to get the results you can just do this:

select * from actual_hourly_2004 where current = 1

You can index the current column too, for best performance.

By the way... using str_replace instead of ereg_replace will help a little, but not nearly as much as minimizing the query results.

[Weedpacket]

Originally posted by dotbob
Secondly, cut down the SQL:

$result = mysql_query('SELECT * FROM Actual_Hourly_2004 ORDER BY Date_Valid, Time_Valid ASC');

I doubt that would speed anything up - if anything, it would be more likely to slow the query down.

As swr noted: if you're not actually using regular expressions, str_replace() is easily faster than ereg_replace(); also, since str_replace() can take arrays for its search and replace arguments, all the Date_Valid changes can be reduced to one call, which would probably be faster than a dozen. Another consideration: if that is a date field in the db, then maybe you can use MySQL's own date functions to format them into your own desired format; or in PHP use strtotime() and date() to convert it to a Unix timestamp and then reformat that. Ditto for Time_Valid

I also wonder if you couldn't just start
the loop with

while ($array[$FMFP_ID] =  = mysql_fetch_assoc ($result)) 
{ 
...

Oh, I see why not - after the loop is finished $array[$FMFP_ID] will be false.

$returned_rows = mysql_num_rows($result);
for($i=0; $i<$returned_rows; $i++)
{ $array[$FMFP_ID] =  = mysql_fetch_assoc ($result);
...

Another thing to consider: if the loop is only supposed to run once, it's not much of a loop, is it? You're not compelled to use mysql_fetch_* functions in a while() loop. I know it seems everyone does it, but I have no idea why.

[skeetley]

Weedpacket,
The loop only needs to run once for each FMFP_ID, not only once.
I can't really make the date/time changes to the database as I get that from someone else. Likewise the records are updated by a commercial program and I can't go make it add a current field. Good ideas though.
Any others?

[skeetley]

OK, I am trying a different approach.

I made my query

$result = mysql_query ('SELECT `FMFP_ID` , `Date_Valid` , `Time_Valid` , `Temp` , `RH` , `CDir` , `WSpd` , `WGst` , `Rn_1`  FROM Actual_hourly_2004 WHERE `Date_Valid` > current_date - interval 2 day ORDER BY `Date_Valid` desc, `Time_Valid` desc'); 

Because I decided that if the recodrd was more than 2 days old then it isn't worth showing.

But now when I do $array[$FMFP_ID]['Temp'] for a station with no data in the past 2 days, well it is not there obviously and no data is shown. How can I account for this and write a line to print "No observations in past 2 days." or something?

[skeetley]

What is a more effective way to write this?

if (!$array['HAULT']['Temp']){$array['HAULT']['Temp']="No";$array['HAULT']['RH']="Recent";$array['HAULT']['CDir']="Observations";}
if (!$array['MCKNZ']['Temp']){$array['MCKNZ']['Temp']="No";$array['MCKNZ']['RH']="Recent";$array['MCKNZ']['CDir']="Observations";}
.
.
.
.
.
.
etc

[swr]

You could create another table with a current
field, then have a process copy new records from
the full table to the table of current data (even
drop the current field, deleting records instead of
setting current = 0), but that's probably more
trouble than it's worth. Limiting by time is probably
good enough.

You could do a "select distinct fmfp_id from
actual_hourly_2004" to get all the IDs, then figure
out which ones are not present in your data
(probably using [man]array_diff[/man]).

As for the if statements... I don't know what
'HAULT' and 'MCKNZ' are, but I'd probably do
something like this:

$keys = array('HAULT', 'MCKNZ');
foreach ($keys as $key) {
    if (!isset($array[$key])) {
        $array[$key] = array(
         'Temp' => 'No',
         'RH' => 'Recent',
         'CDir' => 'Observations',
         );
    }
}

Are HAULT and MCKNZ from fmfp_id's? If so, you
should build the $keys array using "select distinct
fmfp_id from actual_hourly_2004".

[swr]

On second thought, building a table of current data

is probably a good idea. It might make other
things simpler for you too.

Unless there is a primary key column there
already, alter your Actual_Hourly_2004 to add an
"id" column, of type int. Set it to autoincrement
and unique ("unique" will make it indexed too).
This shouldn't break the code that inserts the data
(I can't tell you that it won't, but it shouldn't).
Also, create a table called current_data with all the
same fields as actual_hourly_2004.

Create a function to update the current_data
table. It should do something like this:

select distinct ah.* from actual_hourly_2004 ah,
current_data cd where ah.id > max(cd.id) order
by ah.id

The "distinct" may not be needed. Loop through
the records doing this:
delete from current_data where fmfp_id = $record['fmfp_id']
then this:
insert into current_data ... (all the data you retrieved)

Then just call that function before querying the
current_data table.