Optimize a query multiple tables & Multiple conditions

Question

I need to optimize the query which has multiple joins. And also with multiple OR AND conditions which varies depending upon the user input.

The query which gets generated is:

SELECT  emp.name as entryby, l.cname as location_name, sp.product AS product_name, 
        sp.refcode as pro_refcode, sup.vendorname, sp.mrpgross AS mrpgross,
        pro.cp AS cost_price, cust.name AS customer_names, cust.phone AS mobile_no,
        sp.uom1 as TotalQTY, sp.category_name, auth.*
FROM erp_salesorderproductsdetails sp
LEFT JOIN erp_salesorder s ON s.id = sp.salesorder_id
LEFT JOIN geopos_employees emp ON emp.id = s.entryby
LEFT JOIN geopos_locations l ON s.loc = l.id
LEFT JOIN geopos_productall pro ON pro.product_code = sp.refcode
LEFT JOIN geopos_supplier sup ON sup.id = pro.vendor
LEFT JOIN geopos_customers cust ON cust.id = s.customer
LEFT JOIN authorize_po auth ON auth.salesorder_id = s.id
WHERE sp.salesorder_id = 301 
AND ( sp.category_name IN(SPECTACLES, SPECTACLE LENSES) )
OR sp.salesorder_id = 310 
AND ( sp.category_name IN(SPECTACLES, SPECTACLE LENSES) )
OR sp.salesorder_id = 3234 
AND (sp.category_name IN(SPECTACLES, SPECTACLE LENSES) )

Here, the sp.salesorder_id along with AND in where condition can be multiple depending upon the users input. To retrieve some 700 - 800 records it takes around 2 to 3.5 mins.

But mostly the data counts goes to over 2000. In that case its not performing very efficiently. Can anybody suggest anything ?

I have also shared the Explain result of the query being generated. However, sharing the Table structure won't be a good idea as some tables have columns more than 50.

1   SIMPLE  sp  ALL NULL    NULL    NULL    NULL    6211    Using where 
1   SIMPLE  s   eq_ref  PRIMARY PRIMARY 4   erp_billing.sp.salesorder_id    1       
1   SIMPLE  emp eq_ref  PRIMARY PRIMARY 4   erp_billing.s.entryby   1   Using where 
1   SIMPLE  l   eq_ref  PRIMARY PRIMARY 4   erp_billing.s.loc   1   Using where 
1   SIMPLE  pro ALL NULL    NULL    NULL    NULL    340264  Using where; Using join buffer (flat, BNL join) 
1   SIMPLE  sup eq_ref  PRIMARY PRIMARY 4   erp_billing.pro.vendor  1   Using where 
1   SIMPLE  cust    eq_ref  PRIMARY PRIMARY 4   erp_billing.s.customer  1   Using where 
1   SIMPLE  auth    ALL NULL    NULL    NULL    NULL    13  Using where; Using join buffer (flat, BNL join) 

Answer 1

I suggest adding the following composite keys:

ALTER TABLE erp_salesorderproductsdetails ADD INDEX `idx_1` (`salesorder_id`,`category_name`);
ALTER TABLE geopos_productall ADD INDEX `idx_2` (`product_code`,`vendor`);
ALTER TABLE erp_salesorder  ADD INDEX `idx_3` (`id`,`entryby`,`customer`);

Change the last query part from

WHERE sp.salesorder_id = 301 
AND ( sp.category_name IN(SPECTACLES, SPECTACLE LENSES) )
OR sp.salesorder_id = 310 
AND ( sp.category_name IN(SPECTACLES, SPECTACLE LENSES) )
OR sp.salesorder_id = 3234 
AND (sp.category_name IN(SPECTACLES, SPECTACLE LENSES) )

to

WHERE sp.salesorder_id in (301,310,3234) 
AND  sp.category_name IN('SPECTACLES', 'SPECTACLE LENSES')

EDIT: Add another key on authorize_po table

ALTER TABLE authorize_po  ADD INDEX `salesorder_id` (`salesorder_id`);