SQL query timeout

Adam

Hi guys,

Slight problem with the following script, i keep getting a "fatal error, maximum execution time exceeding 60 seconds" message on this script. Can anyone suggest a more efficient method? The timeout arises from the table tbl_fund_prices having something like half a million entries....

[php]<?php

include 'functions.php';
include 'db.php';

$SQL="SELECT * FROM tbl_fund WHERE fund_inactive != '1'";
$result = mysql_query($SQL) or die(mysql_error());
$num_rows = mysql_num_rows($result);


for($i=0; $i < $num_rows; $i++) {

$row = mysql_fetch_array($result, MYSQL_ASSOC);

$SQL="SELECT DISTINCT fp_fund_id FROM tbl_fund_prices WHERE fp_fund_id={$row}";
mysql_query($SQL) or die(mysql_error());
$row2 = mysql_fetch_array($result2, MYSQL_ASSOC);


}

?>[/php]

oscarBravo

It's not clear what you're trying to do. The only thing you're SELECTing with the second query is a column whose value you already know.

The problem is that you're running lots of queries where one will probably do. If you post the table structures and explain what you want to learn from the queries, you'll probably get the help you need.

There are a few obvious problems anyway: for example, you're trying to mysql_fetch_array($result2), but you haven't assigned $result2.

Adam

Ah yeah sorry, the $result2 thing is a leftover from earlier efforts! Basically the table structure works like this:

tbl_fund contains ~700 unique entries, some of which are inactive, simple layout, it has fund_id, fund_name, fund_inactive and a few other fields irrelevant here.

tbl_fund_prices has ~500,000+ entries, it is updated every day with up to date prices. its fields are fp_id(unique), fp_fund_id(references tbl_fund), fp_insert_date, and fp_price.

Basically there are many entries in tbl_fund_prices for each entry in tbl_fund, I need to select just the most recent entry for each id...and having just typed this I've realised my second query isn't doing what I want for a start!

oscarBravo

Would this give you what you want: [php]$query = "select fp_id, fp_fund_id, max(fp_insert_date) as lastdate, fp_price from tbl_fund_prices, tbl_fund where fund_id = fp_fund_id and fund_inactive != '1' group by fp_fund_id";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
// Do whatever you need to do with each $row here.
}[/php] edit: oops, forgot the fund_inactive criterion.

Adam

That seems to be just the ticket cheers! Any chance you know off the top of your head how i can specify something like:

[PHP]if($row > '21 days old') {
//do something useful[/PHP]

bonkey

Check for support for date arithmetic / date functions.

Then look to either subtract the supplied date from todays date, or to use a function like DATEDIFF (if direct subtraction isn't supported)...

Basically, read up on date arithmetic for your DB. Its generally pretty simple.

<edit>
For optimisation purposes, I'd generally try and write it so you had something like:

where lastdate < (today - 21 days)

rather than

where lastdate + 21 days < today.

The former case means your optimiser can perform a single calculation (today - 21 days) and then use straight comparison against all stored values.

The latter case requires that the addition be calculated seperately for each row.

Adam

Cheers, got that sorted. I'm now trying to write the data to a text file and email it. I gather the data into an array, but then it simply writes the word 'Array' to the text file.... presumably I have to explode the array in this line..??

[PHP]fwrite($handle, $data);[/PHP]

oscarBravo

If you want it to be "human-readable", you'll probably want to do some formatting on it. Check the docs for fprintf().

Adam

Ok, feck the fwrite, I'm just going to lump it into an email body, however I'm still having trouble doing that... i can only get the last row from the query result in the body... here's the code now:

[PHP]<?php

include 'functions.php';
include 'db.php';

$SQL="SELECT fp_id, fp_fund_id, max(fp_insert_date) AS lastdate, fp_price, fund_name FROM tbl_fund_prices, tbl_fund WHERE fund_id = fp_fund_id AND fund_inactive != '1' GROUP BY fp_fund_id";
$result = mysql_query($SQL);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

$previous = date('Y-m-d',strtotime("$when -21 day"));

if($row < $previous) {

$data = array(1=> $row, $row, $row);

print_r($data);
}

}

if(isset($data)){

$mailTo = "adam@blo.ie";
$msgSubject = "Fund Prices";
$msgBody = "$data";
$xHeaders = "From: admin@cron.ie";

mail ($mailTo, $msgSubject, $msgBody, $xHeaders);

}
?>[/PHP]

democrates

Try $data = $data.array(1=> $row/COLOR][/FONT][FONT=Verdana][COLOR=Black]'fund_name'[/COLOR][/FONT][FONT=Verdana][COLOR=Black, $row/COLOR][/FONT][FONT=Verdana][COLOR=Black]'fp_fund_id'[/COLOR][/FONT][FONT=Verdana][COLOR=Black, $row/COLOR][/FONT][FONT=Verdana][COLOR=Black]'lastdate'[/COLOR][/FONT][COLOR=#007700][FONT=Verdana][COLOR=Black)."\n";

As it stands you're overwriting $data with a new value on each loop.


[/COLOR]

oscarBravo

You're overwriting $data every time through the loop. Try [php]$data[] = array($row, $row, $row);[/php]

democrates

Also if tbl_fund_prices continues to grow performance could be hit again down the line.

IIWM I'd set up an index on fp_fund_id, and also use an inner join:

SELECT fp_id, fp_fund_id, max(fp_insert_date) AS lastdate, fp_price, fund_name FROM tbl_fund INNER JOIN tbl_fund_prices ON fund_id = fp_fund_id WHERE fund_inactive != '1' GROUP BY fp_fund_id";

If you need to nail a query right down you can put EXPLAIN at the start of your query, run it in mysqlcc and Mysql will return a table showing how it searches the tables (the 'execution plan'). It's explained in section 7.2.1 of the mysql manual.

Adam

Cheers guys, I caught that overwrite shortly after posting! Thanks for the heads up too democrates, i'll have a read of that section this evening!