Page 1 of 1
SLOW database query
Posted: Sun Jan 11, 2009 12:29 am
by msurabbott
I am having an issue with performance on one of my tables.. the table `file` has 30,000 rows in it, I need to find the files that have a certain type so I came up with this query:
Code: Select all
SELECT COUNT(`file`.file_id) as 'PDF Count' FROM `order`
JOIN `pkg_order_agg` ON (`order`.order_id = `pkg_order_agg`.order_id AND `order`.order_number = 'SEA8002')
JOIN `file_pkg_agg` ON `pkg_order_agg`.pkg_id = `file_pkg_agg`.pkg_id
JOIN `file` ON (`file_pkg_agg`.file_id = `file`.file_id AND `file`.type = 'application/pdf')
The query gets executed multiple times while generating a report for the site, so its accessing and sifting through 30,000 rows multiple times and is wayyy to slow. So I decided to use a temporary table - I create it before looping and access its rows.
Code: Select all
CREATE TEMPORARY TABLE `artfiles` SELECT file_id FROM file WHERE type = 'application/pdf';
SELECT COUNT(`artfiles`.file_id) as 'PDF Count' FROM `order`
JOIN `pkg_order_agg` ON (`order`.order_id = `pkg_order_agg`.order_id AND `order`.order_number = 'SEA8002')
JOIN `file_pkg_agg` ON `pkg_order_agg`.pkg_id = `file_pkg_agg`.pkg_id
JOIN `artfiles` ON (`file_pkg_agg`.file_id = `artfiles`.file_id);
Well come to find out the temp table has 29,000 rows (apparently almost all of the files are of this type) so performance is not improved to any amount that helps..
Any one have any suggestions on how I can improve this..?
Re: SLOW database query
Posted: Sun Jan 11, 2009 12:31 am
by Eran
Probably index optimizations are needed. Run EXPLAIN on the query and post here the results
Re: SLOW database query
Posted: Sun Jan 11, 2009 1:55 am
by msurabbott
Added file:

- EXPLAIN screenshot
- Picture 1.png (36.54 KiB) Viewed 692 times
Re: SLOW database query
Posted: Sun Jan 11, 2009 3:01 am
by Eran
That looks perfectly fine. How long does the query take to complete?
Re: SLOW database query
Posted: Sun Jan 11, 2009 5:38 am
by VladSun
Instead of creating temporary table you can use partitioning:
http://dev.mysql.com/doc/refman/5.1/en/ ... types.html
I think KEY partitioning will be useful in this case.
Also, I tend to use *char typed fields for view purposes only (i.e. they are found only in the SELECT clause) and avoid using them in conditions (where, join on, etc.) and order/group clauses.
Re: SLOW database query
Posted: Sun Jan 11, 2009 5:40 am
by Eran
partioning with only 30,000 rows? this query should actually compute pretty fast. I wonder what else is going on there
Re: SLOW database query
Posted: Sun Jan 11, 2009 5:46 am
by VladSun
I will check the MySQL config files - I always find that the default values for almost everything are set to too small values - like it's supposed to run on 486DX with 2MB of RAM
The partioning thing I mentioned is to show to msurabbott how to implement in the right way what he is trying to do with temporary tables.
You are right - 40K of rows is nothing
PS: Make sure that all ID fields are numeric and not *char.
Re: SLOW database query
Posted: Sun Jan 11, 2009 9:51 am
by msurabbott
Well here is what the script is doing..
It is creating an Excel XML file.. so I write the head with
Code: Select all
header("Content-disposition: attachment; filename=$file");
header("Content-Transfer-Encoding: binary");
header("Content-Type: application/force-download");
header("Content-Length: ".filesize($file));
header("Pragma: no-cache");
header("Expires: 0");
echo "<?xml version=\"1.0\"?>\n";
then begin the xml like so..
Code: Select all
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<LastAuthor></LastAuthor>
<Created>2008-10-23T04:13:12Z</Created>
<Version>12.1</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
<Colors>
<Color>
<Index>1</Index>
<RGB>#D9EACA</RGB>
</Color>
<Color>
<Index>2</Index>
<RGB>#CDCDCD</RGB>
</Color>
then after the headers and styles are defined for the excel sheet I am printing out order information like so (using the mentioned queries)
Code: Select all
while($order = mysql_fetch_object($sqlResult)) {
$openOrder = new Order($order->order_id);
/*$pdfsql = "SELECT COUNT(`file`.file_id) as 'PDFCount' FROM `order`
LEFT OUTER JOIN `pkg_order_agg` ON (`order`.order_id = `pkg_order_agg`.order_id AND `order`.order_number = '". $openOrder->order_number ."')
LEFT OUTER JOIN `file_pkg_agg` ON `pkg_order_agg`.pkg_id = `file_pkg_agg`.pkg_id
LEFT OUTER JOIN `file` ON (`file_pkg_agg`.file_id = `file`.file_id AND `file`.type = 'application/pdf');";
$pdfresult = mysql_query($pdfsql);
$pdfcount = mysql_fetch_object($pdfresult);
$epssql = "SELECT COUNT(`file`.file_id) as 'EPSCount' FROM `order`
JOIN `pkg_order_agg` ON (`order`.order_id = `pkg_order_agg`.order_id AND `order`.order_number = '". $openOrder->order_number ."')
JOIN `file_pkg_agg` ON `pkg_order_agg`.pkg_id = `file_pkg_agg`.pkg_id
JOIN `file` ON (`file_pkg_agg`.file_id = `file`.file_id);";
$epsresult = mysql_query($epssql);
$epscount = mysql_fetch_object($epsresult);*/
switch($openOrder->order_status) {
case "1": $style = "s24"; break;
case "0": $style = "s36"; break;
case "2": $style = "s38"; break;
}
if($o == 0 || (isset($prevOrder) && ($prevOrder->order_status != $openOrder->order_status))) {
?>
<Row ss:Height="13.0">
<Cell ss:StyleID="<?=$style?>"><Data ss:Type="String"><?=strtoupper($openOrder->get_orderStatus())?> ORDERS</Data></Cell>
<Cell ss:StyleID="<?=$style?>"/>
<Cell ss:StyleID="<?=$style?>"/>
<Cell ss:StyleID="<?=$style?>"><Data ss:Type="String">Name Drops</Data></Cell>
<Cell ss:StyleID="<?=$style?>"><Data ss:Type="String">PDFs</Data></Cell>
<Cell ss:StyleID="<?=$style?>"><Data ss:Type="String">ESPs</Data></Cell>
<Cell ss:StyleID="<?=$style?>"/>
<Cell ss:StyleID="<?=$style?>"/>
<Cell ss:StyleID="<?=$style?>"/>
<Cell ss:StyleID="<?=$style?>"/>
<Cell ss:StyleID="<?=$style?>"/>
<Cell ss:StyleID="<?=$style?>"/>
</Row>
<?
}
?>
<Row ss:Height="13.0">
<Cell ss:StyleID="s26"/>
<Cell ss:StyleID="s27"><Data ss:Type="String"><?=$openOrder->order_number?></Data></Cell>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="m432290152"><Data ss:Type="Number"><?=$openOrder->order_dropCount?></Data></Cell>
<Cell ss:StyleID="m432290152"><Data ss:Type="Number"><?=$pdfcount->PDFCount?></Data></Cell>
<Cell ss:StyleID="m432290152"><Data ss:Type="Number"><?=$epscount->EPSCount?></Data></Cell>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
</Row>
<Row ss:Height="13.0">
<Cell ss:StyleID="s26"/>
<Cell ss:StyleID="s28"><Data ss:Type="String"><?=$openOrder->order_description?></Data></Cell>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
<Cell ss:StyleID="s28"/>
</Row>
<Row ss:Height="13.0">
<Cell ss:StyleID="s26"/>
<Cell ss:StyleID="s26"/>
<Cell ss:StyleID="s26"/>
<Cell ss:StyleID="s26"/>
<Cell ss:StyleID="s26"/>
<Cell ss:StyleID="s26"/>
<Cell ss:StyleID="s26"/>
<Cell ss:StyleID="s26"/>
<Cell ss:StyleID="s26"/>
<Cell ss:StyleID="s26"/>
<Cell ss:StyleID="s26"/>
<Cell ss:StyleID="s26"/>
</Row>
<?
$prevOrder = $openOrder;
} // end orders
I think a lot of the processing time of this task is the "writing" of the xml.. but if I have say 4 orders.. this takes 5-10 minutes easy, and if I comment out the two queries (which they are in the posted code) it runs in about 5-10 seconds
Re: SLOW database query
Posted: Sun Jan 11, 2009 10:31 am
by Eran
Where is the query that starts this loop:
Code: Select all
while($order = mysql_fetch_object($sqlResult))
Your script is taking so long because you are querying per iteration (30,000) times. You can probably do one query before and avoid that entirely.
Re: SLOW database query
Posted: Sun Jan 11, 2009 10:49 am
by msurabbott
Code: Select all
$vendorSQL = "SELECT * FROM " . $db->tbl_order . " WHERE vendor_id='" . $_POST['vendor_id'] . "' AND order_status=2";
I dont know how I would do it before hand.. it differs with every iteration of the loop, each order has its own files
Re: SLOW database query
Posted: Tue Jan 13, 2009 9:34 pm
by msurabbott
I really need help on this.. anyone else out there think they could provide a little assistance?