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
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?