SLOW database query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
msurabbott
Forum Commoner
Posts: 33
Joined: Thu Jan 01, 2009 10:18 pm
Location: Chicago, IL, USA

SLOW database query

Post 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..?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SLOW database query

Post by Eran »

Probably index optimizations are needed. Run EXPLAIN on the query and post here the results
msurabbott
Forum Commoner
Posts: 33
Joined: Thu Jan 01, 2009 10:18 pm
Location: Chicago, IL, USA

Re: SLOW database query

Post by msurabbott »

Added file:
EXPLAIN screenshot
EXPLAIN screenshot
Picture 1.png (36.53 KiB) Viewed 693 times
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SLOW database query

Post by Eran »

That looks perfectly fine. How long does the query take to complete?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: SLOW database query

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SLOW database query

Post by Eran »

partioning with only 30,000 rows? this query should actually compute pretty fast. I wonder what else is going on there
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: SLOW database query

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
msurabbott
Forum Commoner
Posts: 33
Joined: Thu Jan 01, 2009 10:18 pm
Location: Chicago, IL, USA

Re: SLOW database query

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: SLOW database query

Post 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.
msurabbott
Forum Commoner
Posts: 33
Joined: Thu Jan 01, 2009 10:18 pm
Location: Chicago, IL, USA

Re: SLOW database query

Post 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
msurabbott
Forum Commoner
Posts: 33
Joined: Thu Jan 01, 2009 10:18 pm
Location: Chicago, IL, USA

Re: SLOW database query

Post by msurabbott »

I really need help on this.. anyone else out there think they could provide a little assistance?
Post Reply