Help optimizing very slow mySQL query

Question

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;

Answer 1

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