# Optimize query with indexes all over the place

**URL:** <https://forums.percona.com/t/optimize-query-with-indexes-all-over-the-place/301>\
**Category:** Other MySQL® Questions\
**Created:** [April 25, 2007, 3:43pm UTC](https://forums.percona.com/t/optimize-query-with-indexes-all-over-the-place/301 "2007-04-25T15:43:14Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![mysqlNewDBA](https://avatars.discourse-cdn.com/v4/letter/m/cab0a1/32.png) [@mysqlNewDBA](https://forums.percona.com/u/mysqlNewDBA)\
**Post date:** [April 25, 2007, 3:43pm UTC](https://forums.percona.com/t/optimize-query-with-indexes-all-over-the-place/301/1 "2007-04-25T15:43:14Z")

</div>

I need some help optimizing the following query  
SELECT sql\_calc\_found\_rows ob.order\_batch\_idnum,  
oi.order\_idnum,  
ost.order\_stock\_type\_idnum,  
oag.order\_activity\_group\_idnum,  
ob.request\_contact\_idnum,  
oag.order\_group\_idnum,  
oi.attention,  
oi.customer\_name,  
oi.address\_city,  
oi.state\_code,  
DATE\_FORMAT(oi.ship\_by\_date,‘%m/%d/%Y’) AS ship\_by\_date,  
CONCAT\_WS(’ ‘,rcon.name\_first,rcon.name\_last) AS requested\_by,  
CONCAT\_WS(’ ‘,mcon.name\_first,mcon.name\_last) AS placed\_by,  
IF(ost.print\_pick\_ticket\_date \> ‘0000-00-00 00:00:00’,  
DATE\_FORMAT(ost.print\_pick\_ticket\_date,’%m/%d/%Y’),  
‘none’) AS print\_pick\_ticket\_date,  
DATE\_FORMAT(ob.order\_date,‘%m/%d/%Y’) AS order\_date,  
DATE\_FORMAT(t.ship\_date,‘%m/%d/%Y’) AS ship\_date,  
DATE\_FORMAT(ost.invoice\_date,‘%m/%d/%Y’) AS invoice\_date,  
IF(ost.deliver\_date,DATE\_FORMAT(ost.deliver\_date,‘%m/%d/%Y’) ,  
IF(oag.deliver\_date,DATE\_FORMAT(oag.deliver\_date,‘%m/%d/%Y’) ,  
‘’)) AS deliver\_date,  
sv.status\_name,  
ost.current\_status,  
oa.master\_customer\_idnum,  
pst.product\_stock\_type,  
pl.plant\_name,  
(SELECT SUM(oa\_count.quantity\_requested)  
FROM order\_activity oa\_count  
WHERE oa\_count.order\_stock\_type\_idnum = oa.order\_stock\_type\_idnum  
GROUP BY oa\_count.order\_stock\_type\_idnum) AS qty\_total,  
CONCAT(IF(NOT ISNULL(oi.address\_street1),CONCAT(oi.address\_street1,’ ‘),  
‘’),IF(NOT ISNULL(oi.address\_street2),CONCAT(oi.address\_street2,’  
‘),  
‘’),IF(NOT ISNULL(oi.address\_city),CONCAT(’‘,oi.address\_city),  
‘’),IF(NOT ISNULL(oi.country\_division\_idnum)  
AND oi.country\_division\_idnum != 0,CONCAT(’, ‘,cd.division\_code),  
‘’),IF(NOT ISNULL(oi.address\_zip5),CONCAT(’ ‘,oi.address\_zip5),  
‘’),IF(NOT ISNULL(oi.country\_idnum)  
AND oi.country\_idnum != 0,CONCAT(’  
‘,c.country),  
‘’)) AS ship\_address,  
IF(MAX(os.time\_stamp) \> ‘0’,DATE\_FORMAT(MAX(os.time\_stamp),’%m/%d/%Y’),  
‘none’) AS approved\_date,  
pur.purpose\_name,  
CONCAT(pro.promotion\_name,‘, ‘,pta.promotion\_activity\_name) AS promotion\_name,  
cus.customer\_name AS client\_name,  
oi.country\_idnum,  
oi.address\_zip5,  
sp.carrier\_service\_idnum,  
IF(ost.deliver\_date,ost.deliver\_date,IF(oag.deliver\_date,oag .deliver\_date,’’)) AS need\_date\_timestamp,  
lb.label\_name,  
dist.customer\_name AS dist\_of\_record,  
oag.order\_comments,  
t.order\_id,  
sp.ship\_priority\_idnum,  
(SELECT COUNT(t\_count.tracking\_idnum) AS tracking\_count  
FROM shipping.tracking t\_count  
WHERE t\_count.order\_stock\_type\_idnum = ost.order\_stock\_type\_idnum  
AND t\_count.void != 1  
GROUP BY t\_count.order\_stock\_type\_idnum) AS tracking\_summary,  
oi.address\_zip5,  
oi.country\_idnum,  
sp.ship\_priority\_idnum,  
sp.carrier\_service\_idnum,  
IF(ost.deliver\_date,ost.deliver\_date,IF(oag.deliver\_date,oag .deliver\_date,‘’)) AS need\_date\_timestamp,  
pst.food\_stock\_type\_idnum,  
ot.operation\_type,  
cus\_usr.customer\_idnum  
FROM order\_stock\_type ost  
LEFT JOIN order\_activity oa  
ON (oa.order\_stock\_type\_idnum = ost.order\_stock\_type\_idnum)  
AND (oa.kit\_master\_idnum = 0)  
LEFT JOIN order\_info oi  
ON oi.order\_idnum = ost.order\_idnum  
LEFT JOIN order\_batch ob  
ON ob.order\_batch\_idnum = ost.order\_batch\_idnum  
LEFT JOIN order\_activity\_group oag  
ON oag.order\_activity\_group\_idnum = ost.order\_activity\_group\_idnum  
LEFT JOIN order\_status os  
ON oa.order\_activity\_idnum = os.order\_activity\_idnum

LEFT JOIN product\_stock\_type pst  
ON ost.product\_stock\_type\_idnum = pst.product\_stock\_type\_idnum  
LEFT JOIN status\_value sv  
ON sv.status\_idnum = ost.current\_status  
LEFT JOIN shipping.tracking t  
ON t.order\_stock\_type\_idnum = ost.order\_stock\_type\_idnum  
LEFT JOIN product p  
ON p.product\_idnum = oa.product\_idnum  
LEFT JOIN contact rcon  
ON rcon.contact\_idnum = ob.request\_contact\_idnum  
LEFT JOIN contact mcon  
ON mcon.contact\_idnum = ob.master\_contact\_idnum  
LEFT JOIN general.country\_division cd  
ON cd.country\_division\_idnum = oi.country\_division\_idnum  
LEFT JOIN general.country c  
ON c.country\_idnum = oi.country\_idnum  
LEFT JOIN plant\_location pl  
ON oag.plant\_location\_idnum = pl.plant\_location\_idnum  
LEFT JOIN purpose pur  
ON pur.purpose\_idnum = ob.purpose\_idnum  
LEFT JOIN promotion.promotion\_to\_activity pta  
ON pta.promotion\_to\_activity\_idnum = ob.promotion\_to\_activity\_idnum  
LEFT JOIN promotion.promotion pro  
ON pro.promotion\_idnum = pta.promotion\_idnum  
LEFT JOIN customer cus\_usr  
ON cus\_usr.customer\_idnum = ob.request\_customer\_idnum  
LEFT JOIN customer cus  
ON oa.master\_customer\_idnum = cus.customer\_idnum  
LEFT JOIN operation\_type ot  
ON ot.operation\_type\_idnum = cus\_usr.operation\_type\_idnum  
LEFT JOIN ship\_priority sp  
ON oag.ship\_priority\_idnum = sp.ship\_priority\_idnum  
LEFT JOIN customer\_stock\_type cst  
ON (cst.master\_customer\_idnum = ost.master\_customer\_idnum  
AND cst.stock\_type\_idnum = ost.product\_stock\_type\_idnum)  
LEFT JOIN accounting.internal\_account aia  
ON aia.internal\_account\_idnum = cst.proteus\_internal\_account\_idnum  
LEFT JOIN `label` lb  
ON lb.label\_idnum = ob.label\_idnum  
LEFT JOIN customer dist  
ON dist.customer\_idnum = oi.distributor\_idnum  
WHERE ost.order\_stock\_type\_idnum BETWEEN 162760  
AND 218053  
AND DATE\_FORMAT(ob.order\_date,‘%Y%m%d’) = ‘20070425’  
AND os.status\_idnum IN (3,41)  
GROUP BY ost.order\_stock\_type\_idnum  
ORDER BY ost.order\_stock\_type\_idnum  
LIMIT 0,100

I ran an explain on it and got the following result

id select\_type table type possible\_keys key key\_len ref rows Extra  
1 PRIMARY os range status\_idnum,order\_activity\_idnum status\_idnum 4 705961 Using where; Using temporary; Using filesort  
1 PRIMARY oa eq\_ref PRIMARY,order\_stock\_type\_idnum,kit\_master\_idnum PRIMARY 4 shopping.os.order\_activity\_idnum 1 Using where  
1 PRIMARY ost eq\_ref PRIMARY,order\_batch\_idnum PRIMARY 4 shopping.oa.order\_stock\_type\_idnum 1   
1 PRIMARY ob eq\_ref PRIMARY PRIMARY 4 shopping.ost.order\_batch\_idnum 1 Using where  
1 PRIMARY oi eq\_ref PRIMARY PRIMARY 4 shopping.ost.order\_idnum 1   
1 PRIMARY pst eq\_ref PRIMARY PRIMARY 8 shopping.ost.product\_stock\_type\_idnum 1   
1 PRIMARY oag eq\_ref PRIMARY PRIMARY 4 shopping.ost.order\_activity\_group\_idnum 1   
1 PRIMARY sv eq\_ref PRIMARY PRIMARY 4 shopping.ost.current\_status 1   
1 PRIMARY t ref order\_stock\_type\_idnum order\_stock\_type\_idnum 4 shopping.oa.order\_stock\_type\_idnum 2   
1 PRIMARY p eq\_ref PRIMARY PRIMARY 4 shopping.oa.product\_idnum 1 Using index  
1 PRIMARY rcon eq\_ref PRIMARY PRIMARY 4 shopping.ob.request\_contact\_idnum 1   
1 PRIMARY mcon eq\_ref PRIMARY PRIMARY 4 shopping.ob.master\_contact\_idnum 1   
1 PRIMARY cd eq\_ref PRIMARY PRIMARY 8 shopping.oi.country\_division\_idnum 1   
1 PRIMARY c eq\_ref PRIMARY PRIMARY 8 shopping.oi.country\_idnum 1   
1 PRIMARY pl eq\_ref PRIMARY PRIMARY 8 shopping.oag.plant\_location\_idnum 1   
1 PRIMARY pur eq\_ref PRIMARY PRIMARY 4 shopping.ob.purpose\_idnum 1   
1 PRIMARY pta eq\_ref PRIMARY PRIMARY 8 shopping.ob.promotion\_to\_activity\_idnum 1   
1 PRIMARY pro eq\_ref PRIMARY PRIMARY 8 promotion.pta.promotion\_idnum 1   
1 PRIMARY cus\_usr eq\_ref PRIMARY PRIMARY 4 shopping.ob.request\_customer\_idnum 1   
1 PRIMARY cus eq\_ref PRIMARY PRIMARY 4 shopping.oa.master\_customer\_idnum 1   
1 PRIMARY ot eq\_ref PRIMARY PRIMARY 8 shopping.cus\_usr.operation\_type\_idnum 1   
1 PRIMARY sp eq\_ref PRIMARY PRIMARY 8 shopping.oag.ship\_priority\_idnum 1   
1 PRIMARY cst ref stock\_type\_idnum,master\_customer\_idnum stock\_type\_idnum 9 shopping.ost.product\_stock\_type\_idnum 4   
1 PRIMARY aia eq\_ref PRIMARY PRIMARY 8 shopping.cst.proteus\_internal\_account\_idnum 1 Using index  
1 PRIMARY lb eq\_ref PRIMARY PRIMARY 4 shopping.ob.label\_idnum 1   
1 PRIMARY dist eq\_ref PRIMARY PRIMARY 4 shopping.oi.distributor\_idnum 1   
3 DEPENDENT SUBQUERY t\_count ref VOID,order\_stock\_type\_idnum order\_stock\_type\_idnum 4 shopping.ost.order\_stock\_type\_idnum 2 Using where  
2 DEPENDENT SUBQUERY oa\_count ref order\_stock\_type\_idnum order\_stock\_type\_idnum 5 shopping.oa.order\_stock\_type\_idnum 3 Using where

---

<div class="post-metadata">

**Author:** ![mysqlNewDBA](https://avatars.discourse-cdn.com/v4/letter/m/cab0a1/32.png) [@mysqlNewDBA](https://forums.percona.com/u/mysqlNewDBA)\
**Post date:** [April 25, 2007, 4:12pm UTC](https://forums.percona.com/t/optimize-query-with-indexes-all-over-the-place/301/2 "2007-04-25T16:12:25Z")

</div>

I just remembered about having functions on the RHS of the equal sign of the where clause.  
I got rid of that and the performance quadrupled 😃