sub-select major query help needed

speedamp

hi everybody,

i've inherited a MAJORLY convoluted sql statement that my client is using for a download file (using opencart).

this works well with smaller data sets, but their db now has over 30k orders, and the multiple sub-selects are locking things up.

for your amusement, the SQL statement is pasted below.

any guidance on where to start tearing this apart would be much appreciated,

-Michael

SELECT o.order_id, o.firstname as firstname,o.lastname as lastname, o.telephone, o.email,e.type as whoPays,o.shirt,o.gender,o.usatf, o.usat, o.day,o.month,o.year, 
CONCAT(o.payment_address_1, ' ', o.payment_address_2)  AS address,
o.payment_city as city, o.payment_postcode as zip, o.payment_zone as state,op.discount as discount, op.charity as charity,op.fee as fee, 
op.one_costItemQuantity as costItemOneQty,op.one_costItemId,op.two_costItemQuantity as costItemTwoQty,op.two_costItemId,op.three_costItemQuantity as costItemThreeQty,op.three_costItemId,
op.total as total,op.price as racePrice, discountId AS discountName, s.name as special, r.name as race,
(SELECT c.teamNameDisplay FROM captain as c WHERE o.teamName = c.id ) AS team,
(SELECT teamCategory.name FROM teamCategory LEFT OUTER JOIN captain on captain.teamCategory_id = teamCategory.id WHERE o.teamName = captain.id ) AS teamType,
(SELECT charity.name FROM charity WHERE op.charityId= charity.id ) AS charityName,
(SELECT costitem.name FROM costitem WHERE op.one_costItemId=costitem.id ) AS costItemOneName,
(SELECT costitem.price FROM costitem WHERE op.one_costItemId=costitem.id ) AS costItemOnePrice,
(SELECT costitem.name FROM costitem WHERE op.two_costItemId=costitem.id ) AS costItemTwoName,
(SELECT costitem.price FROM costitem WHERE op.two_costItemId=costitem.id ) AS costItemTwoPrice,
(SELECT costitem.name FROM costitem WHERE op.three_costItemId=costitem.id ) AS costItemThreeName,
(SELECT costitem.price FROM costitem WHERE op.three_costItemId=costitem.id ) AS costItemThreePrice,
(SELECT toBill FROM captain WHERE captain.id = o.teamName ) AS toBill,
o.total, o.currency_code, o.currency_value, o.date_added, o.date_modified, o.payment_code 
FROM `opc_order` o 
LEFT OUTER JOIN opc_order_product op on op.order_id = o.order_id 
LEFT OUTER JOIN eventRegistration e  on o.eventId =e.event_id  

LEFT OUTER JOIN special as s on s.id = o.specialId 
LEFT OUTER JOIN race as r on r.id = o.raceId 
WHERE o.order_status_id > '0' AND o.eventId = '{$xxxxxxx}' 
ORDER BY race,o.order_id DESC

NogDog

While refactoring the query may well be warranted, the first thing I'd do is run it through EXPLAIN and see if adding some indexes where needed would get you running at an acceptable speed again.

dalecosp

NogDog;11028633 wrote:
While refactoring the query may well be warranted, the first thing I'd do is run it through EXPLAIN and see if adding some indexes where needed would get you running at an acceptable speed again.

Hear, hear. In addition, or perhaps EXPLAIN will help ... beware of Cartesian joining (looks OK at first glance but it's complex enough it makes me wonder if some ANDs after the ONs are needed, but my brain's cooked waiting for vacation tomorrow....).

FWIW, I like "mytop" for server tuning; it could be that some changes to my.cnf would be helpful as well.

Weedpacket

LEFT JOINs can also clobber index use, because the criterion doesn't place any constraints on the left-hand relation (it needs to pull results from the left even when they don't match anything on the right). If some of those can be made INNER JOINs without changing the meaning of the query (i.e., every record in the relation on the left supposed to have one in the one on the right, e.g. opc_order and opc_order_product, or opc_order and eventRegistration) then that could improve index usage.