Can't get this query below 3.5 seconds?

[Andy-H]

 

I have this query, and I'm using explain extended and optimising with an "INDEX" type index on the orders table, I've got it down from 6.5 to 3.5 seconds with an index on id, orderid, saleby.

SELECT 
   o.orderid,
   o.ordername              AS ref,
   ROUND(SUM(totalcost), 2) AS total,
   IF(
      LENGTH(c.company),
      c.company,
      CONCAT(c.title, ' ', c.FirstName, ' ', c.Surname)
   )                        AS customer,
   o.ordertime
FROM orders o USE INDEX ( last_5_orders )
   INNER JOIN ( orderinfo oi ) USING ( orderid )
   INNER JOIN ( customer c )   ON ( c.custid  = o.customerid )
WHERE o.saleby = 1
   GROUP BY o.orderid
   ORDER BY o.id DESC
LIMIT 5

 

 

Could anyone point me in the right direction? Or a decent tutorial on how to optimise using explain extended.

 

 

Cheers, Andy

[mattclements]

Could you provide the SQL Statement to Create the DB & Tables and provide some sample data which produces the 3.5 second times and I can analyse and improve for you.

 

Regards,

Matt

[The Little Guy]

Have you tried it without "USE INDEX ( last_5_orders )"? Or is that what you added to speed it up?

[DavidAM]

The Explain Plan indicates it is not using an index; it is doing a table scan.

 

... got it down from 6.5 to 3.5 seconds with an index on id, orderid, saleby.

 

Is that a composite index --- all three fields? Since your WHERE clause specifies only one criteria, and it is on "saleby", that index will not likely be chosen for use. You should turn the fields around so that the "saleby" is first --- saleby, orderid, id. I've heard that mySql stores the PRIMARY KEY in the index anyway, so that column may not be needed -- (I heard that on a forum, but have never confirmed it).

 

How selective is "saleby"? You have over 61,000 rows in that table, are there a significant number of discreet values for "saleby" and are they fairly evenly distributed? My point is, if that column is a boolean, so there are only two values (0 and 1), then it will not be very selective, anyway (about half the table), and may not be useful.

 

Have you OPTIMIZEd the tables or ANALYZEd the tables since loading the data? Adding an index to a table automatically does this, and may be where your improvement came from. ANALYZE rebuilds the distribution data so the engine can make more informed decisions about how well a particular index will help. OPTIMIZE rebuilds the indexes, with the same affect, but also rearranges the index pages to make access faster --- that's a simplified explanation.

 

As Matt suggested, we need more information about the database structure to help determine how to optimize that query. It would also help to know what it is you are trying to retrieve. The name you choose for the index -- "last 5 orders" -- seems to imply that you want recent orders, but the WHERE clause -- saleby = 1 -- seems to indicate something else. Show us the table definitions and tells us what you are after, and we may be able to help.