CODEWITHSUNDEEP
phpmysqldatabase
redstang423
redstang423

Reputation: 133

I need help troubleshooting why mySQL queries are taking longer than expected

I am troubleshooting why a php script takes so long to run and can sometimes time out. I have a database filled with product information (~50,000 total records) from a handful (9 total) of suppliers. The script pulls an XML file containing list of products that have changes from each supplier's feed, finds that record, then modifies it appropriately.

The query to find the single product record that matches the supplier's SKU and supplier ID in the product table is taking between 0.25 seconds and 0.5 seconds, so a small update of ~400 rows is taking over 2 minutes. I am new to database performance, and I am struggling to understand why I have a very simple query that's taking an incredibly long time to run.

I first commented out every query where it searched the database to see how long the script itself would take to iterate through the XML files. It would iterate through the 400 records in 1-2 seconds. I then added in the first query that is encountered in the iteration, which was executing the search to find the correct entry in the table for the product called out in the XML file. This is where the timing jumped to a 2 minute runtime for the script.

The iteration code, summarized for the relevant parts:

$checkq = "SELECT * FROM productLinks WHERE affiliateSKU=:sku AND supplierID=:sid";
$checkqs = $DBHa->prepare($checkq);

$xml = simplexml_load_file($supplierURL,"SimpleXMLElement",LIBXML_NOCDATA);
foreach($xml->children() as $products) { 
 //Set variables from the XML

 $checkqs->execute(array(':sku'=>$sku, ':sid'=>$supplierID)); //This is the problem query
 //I capture the number of results (should always be 1 or 0), 
 //and modify the entry if the result is 1, and insert a new entry if 0)
}

In my research for what the problem could be, I picked a random entry and ran the above query directly in phpMyAdmin and had times anywhere from 0.1 to 0.75 seconds. I used some troubleshooting steps I found while researching, but can't figure out what it really means.

I used the Explain statement, and returned the following:

id | select_type | table        | type | possible_keys | key        | key_len | ref   | rows  | Extra
1  | SIMPLE      | productLinks | ref  | supplierID    | supplierID | 4       | const | 11827 | Using where

I then ran Profile on the query, and while the query itself took 0.2218 seconds to return the 1 result according to phpMyAdmin, the Profile results returned a total of 0.00293 seconds as follows:

Status                Duration
starting              0.000132
checking permissions  0.000009
Opening tables        0.000007
init                  0.000011
optimizing            0.000007
executing             0.000010
end                   0.000007
query end             0.000006
closing tables        0.000019
freeing items         0.000075
cleaning up           0.000010

Can anyone help me understand exactly what is making my query take so long, and how I might be able to improve performance?

Upvotes: 3

Views: 123

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

For better performance be sure you have a composite index on 

table  productLinks 

columns  (affiliateSKU, supplierID)

Upvotes: 4

Related Questions