Re: How can I improve this table and query
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
LIMIT 10
Hope this helps,
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway