How to reduce numbers of AND used in this MySQL query?

Question

This is the query I am working on and somehow it takes to much time and eventually times out, which makes me think if I can reduce the number of AND in the WHERE Clause. I am new to this Huge Queries.

SELECT sfog.entity_id, 
(
CASE 
    WHEN sfog.status = 'delivered' THEN 'Order'
    WHEN sfog.status IN ('return','rtndelivered','closed') THEN 'Return Order'
    WHEN sfog.status = 'CanceledBS' THEN 'CanceledBS Order'
END) AS Type,

CONCAT(cev1.value,' ', cev2.value) AS 'Vendor Name',
sfog.status AS Status,
sfog.increment_id AS OrderNo ,
sfosh.created_at AS 'Invoice Date',
mo.tracking_number AS 'Tracking Number',
CONCAT(sfoa.firstname,' ', sfoa.lastname) AS 'Customer Name',
CONCAT(sfoa.street,' ',sfoa.city,' ',sfoa.region,' ',sfoa.postcode) AS 'Address',
sfoa.email AS 'Email',
group_concat( DISTINCT sfoi.sku SEPARATOR ', ') AS `Product Name`,
sfo.total_qty_ordered AS 'Qty',
SUM(ms.totalamountut) AS 'Order Value'
FROM sales_flat_order_grid sfog, 
  sales_flat_order_status_history sfosh,
  customer_entity_varchar cev1, 
  customer_entity_varchar cev2, 
  marketplace_orders mo, 
  sales_flat_order_address sfoa, 
  sales_flat_order_item sfoi, 
  sales_flat_order sfo, 
  marketplace_saleslist ms
WHERE sfog.status IN ('delivered','return','rtndelivered','closed','CanceledBS') 
 and sfosh.parent_id = sfog.entity_id 
 and cev1.attribute_id = '5' 
 and cev2.attribute_id = '7' 
 and mo.seller_id = cev1.entity_id 
 and mo.seller_id = cev2.entity_id 
 and mo.order_id = sfog.entity_id 
 and sfoa.parent_id = sfog.entity_id 
 and sfoi.order_id = sfog.entity_id 
 and sfo.entity_id = sfog.entity_id 
 and ms.mageorderid = sfog.entity_id 
GROUP BY sfog.entity_id;

Yes, use explicit `JOIN`. Also your `GROUP BY sfog.entity_id` only one column so your resultset is probably not stable. — Lukasz Szozda, Aug 08 '17 at 11:44
@lad2025 Can you please help with using Explicit Join.. I dont have much idea about it — Urja, Aug 08 '17 at 11:56
**[Inner join vs Where](https://stackoverflow.com/questions/121631/inner-join-vs-where)** — Lukasz Szozda, Aug 08 '17 at 11:57
@lad2025 This has only 2 tables .. I have more than 8 tables .. How to join all of them ? Or can I do something like - t1.a = t2.b = t3.c = t4.d ? — Urja, Aug 08 '17 at 12:12
JOINing table is basic topic. Please read about it first before you try to write more complex queries. — Lukasz Szozda, Aug 08 '17 at 12:13
May we see what indexes you have on your various tables? Also, please obtain an explain plan for the query as it stands. — halfer, Aug 08 '17 at 21:52

score 2 · Accepted Answer · answered Aug 08 '17 at 22:31

Reducing the number of "and"s in the query has got very little to do with the query performance. If you had looked at some of the many questions posted here on improving query performance you would have noticed that those which are upvoted and answered include many featires missing from your post, like schemas and explain plans.

This is going to be a very heavy query to run - the only problem we can see is the number of tables. With 9 tables you will need at least 8 joining predicates. And that is what we see in your query mixed up with three filter predicates using literals. Its quite possible that some of those tables are redundant in this query, but we cannot determine that.

"Huge queries" are no different in their composition nor optimization than short, simple ones.

Hello @symcbean, The tables that i have used here have unique values, and I feel you are right that high number of tables is causing timeout. — Urja, Aug 09 '17 at 06:40

score 0 · Answer 2 · answered Aug 08 '17 at 11:58

Join like this:

 FROM sales_flat_order_grid sfog 
 JOIN sales_flat_order_status_history sfosh 
   ON sfosh.parent_id = sfog.entity_id
 JOIN marketplace_orders mo
   ON mo.order_id = sfog.entity_id
 JOIN customer_entity_varchar cev1
   ON mo.seller_id = cev1.entity_id
 JOIN customer_entity_varchar cev2 
   ON mo.seller_id = cev2.entity_id
 JOIN sales_flat_order_address sfoa
   ON sfoa.parent_id = sfog.entity_id
 JOIN sales_flat_order_item sfoi
   ON sfoi.order_id = sfog.entity_id 
 JOIN sales_flat_order sfo
   ON sfo.entity_id = sfog.entity_id
 JOIN marketplace_saleslist ms
   ON ms.mageorderid = sfog.entity_id
WHERE sfog.status IN ('delivered','return','rtndelivered','closed','CanceledBS') 
  AND cev1.attribute_id = '5' 
  AND cev2.attribute_id = '7' 
GROUP BY sfog.entity_id;

It makes your code way easier to read through

do the tables have indices? there is no way i am aware of that would make the joins faster... or can you up the timeout threshhold? Or can you create temp tables? then maybe join in pairs or something. — RealCheeseLord, Aug 08 '17 at 12:51

How to reduce numbers of AND used in this MySQL query?

2 Answers2