0

I have a query which takes about 6 secs to execute.

SELECT * FROM
(SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, user.firstname, user.lastname, op.ID AS prouctOrderID, sp.ID AS productID, user.userID
   FROM table_orders_1_products AS op
   LEFT JOIN table_orders_1 AS ord ON ord.ID=op.orderID
   LEFT JOIN cms_user_aditional AS USER ON ord.userAdditional=USER.ID
   LEFT JOIN table_status AS stat ON stat.ID=op.statusID
   LEFT JOIN table_product_1 AS sp ON op.productID=sp.ID
   LEFT JOIN table_partners AS spp ON sp.supplier=spp.ID
   WHERE op.statusID!='0'
   ORDER BY ord.ID DESC LIMIT 1000) AS allO
ORDER BY allO.ID DESC

If I remove the "bolded" order by statement it takes only 40ms.

Is there any work around to fix this issue?

Create table for test_orders_1

CREATE TABLE `com_shop_orders_1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `date` int(11) NOT NULL,
  `email` text NOT NULL,
  `name` text,
  `userID` int(11) DEFAULT NULL,
  `status` int(3) NOT NULL DEFAULT '1',
  `productsTotal` double NOT NULL,
  `total` double NOT NULL,
  `tax` double NOT NULL,
  `discount` double NOT NULL,
  `shippingID` int(3) NOT NULL,
  `shippingPrice` double NOT NULL,
  `paymentID` int(3) NOT NULL,
  `userAdditional` int(11) NOT NULL,
  `transacitonID` text NOT NULL,
  `paymentPrice` double NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `userID` (`userID`),
  KEY `shippingID` (`shippingID`),
  KEY `paymentID` (`paymentID`),
  KEY `ID_index` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=208180 DEFAULT CHARSET=latin1;
  • 2
    Please format your code. And why do you have the derived table (all0)? – ypercubeᵀᴹ Jul 11 '14 at 10:18
  • 1
    Show us the output of SHOW CREATE TABLE table_orders_1_products ; – ypercubeᵀᴹ Jul 11 '14 at 13:04
  • What are the possible values of status? Only 0 and 1? Or are there others as well? – ypercubeᵀᴹ Jul 14 '14 at 18:21
2

See this article about the performance problems and possible solutions using ORDER BY ... LIMIT.

I would create an index on ord.ID DESC and remove the subquery. This is assuming that ID is not a primary key and indexed already.

SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, user.firstname, user.lastname, op.ID AS prouctOrderID, sp.ID AS productID, user.userID
FROM table_orders_1_products AS op
LEFT JOIN table_orders_1 AS ord ON ord.ID=op.orderID
LEFT JOIN cms_user_aditional AS USER ON ord.userAdditional=USER.ID
LEFT JOIN table_status AS stat ON stat.ID=op.statusID
LEFT JOIN table_product_1 AS sp ON op.productID=sp.ID
LEFT JOIN table_partners AS spp ON sp.supplier=spp.ID
WHERE op.statusID!='0'
ORDER BY ord.ID DESC LIMIT 1000

See this page on creating indexes. (Or a Primary Key if it does not already exist. Creating a primary key on unique columns, usually ID's in this case, will help as they are automatically indexed.)

To add a Primary Key: ALTER TABLE table_orders_1 ADD PRIMARY KEY(ID)

To create an Index: CREATE INDEX ID_index ON table_orders_1 (ID DESC)

EDIT:

In general it is good practice to look at and understand your execution plans to see if you can identify any performance bottlenecks. See if you can narrow the scope of your table selects, for example, do you really need to LEFT JOIN to all the tables in your query? INNER JOIN is typically faster, unless the tables are very small (~10 rows) or if the tables do not have sufficient indexes to cover the query. Also on second examination it does not look like you are selecting anything from table_partners SPP, if not it does not need to be included. It may also be faster to split the table joins into two queries like below.

SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, ord.userAdditional, op.statusID, op.productID
INTO #TEMP
FROM table_orders_1_products AS op
INNER JOIN table_orders_1 AS ord 
  ON ord.ID=op.orderID 
  AND op.statusID <> '0'
ORDER BY ord.ID DESC LIMIT 1000

SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, user.firstname, user.lastname, op.ID AS prouctOrderID, sp.ID AS productID, user.userID
FROM #TEMP T
INNER JOIN cms_user_aditional AS USER 
  ON T.userAdditional = USER.ID
INNER JOIN table_status AS stat 
  ON stat.ID = T.statusID
INNER JOIN table_product_1 AS sp 
  ON T.productID = sp.ID
  • Any chance you could add code for the index? – Max Vernon Jul 11 '14 at 13:11
  • CREATE INDEX: CREATE INDEX ID_index ON table_orders_1 (ID DESC) – Christopher Jul 11 '14 at 13:17
  • Welcome to Stack Exchange! For the benefit of future visitors, you could edit your answer to show the CREATE INDEX ... statement. Sometimes comments will get auto-deleted if they are deemed "too chatty". That likely won't happen here, but I just wanted you to know! – Max Vernon Jul 11 '14 at 13:27
  • Its a little better, but its still slow when the query is runned first time. I have added create table for test_orders_1 in question if it helps.. – GarG Jul 12 '14 at 7:20
  • Any other suggestions? :/ – GarG Jul 14 '14 at 10:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.