MySQL Forums
Forum List  »  Performance

Re: How can I improve this table and query
Posted by: Øystein Grøvlen
Date: February 17, 2016 06:07AM

Hi Adam,

I will focus on the query, and leave comments about schema to others.

First question: Which version are you on? Based on the query plan, I
would guess 5.5. You should definitely consider upgrading to a newer
version since there are several improvements to the optimizer in both
MySQL 5.6 and 5.7 that this query could benefit from.

I admit I have not fully grasped the intent of the query, but it seems
to find number of orders per brand, but only counting transactions
that included one specific brand. Does that sound correct?

Some advice:

I do not see why the nationalities table is included in the subquery.
It does not seem to be used.

AFAICT, the following query should be equivalent, and open up for more
optimizations if using MySQL 5.6 or later:

SELECT `products`.`brand`, COUNT(DISTINCT transaction_data.txn_id) AS numOrders
FROM `products`
INNER JOIN `transaction_data` USING(sku)
INNER JOIN `nationalities` USING(nationality_id)
WHERE `transaction_data`.txn_id IN
(SELECT `transaction_data`.`txn_id`
FROM `transaction_data` INNER JOIN `products` USING (sku)
WHERE brand = <Brand>) AND
brand <> <Brand> AND
nationality LIKE '%' AND
Yr = '2014' AND
YrQtr LIKE '%'
GROUP BY brand
ORDER BY numOrders DESC, brand ASC

Hope this helps,

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway

Options: ReplyQuote

Written By
February 17, 2016 04:49AM
Re: How can I improve this table and query
February 17, 2016 06:07AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.