This question is an extension of this question. Where Rick James kindly provided a list of things I should change in my table and query.
I also changed my application so that unused filters weren't included in the query at all.
Now my table looks like this:
CREATE TABLE `transaction_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`txn_id` varchar(32) NOT NULL,
`nationality_id` smallint(6) NOT NULL,
`tdate` date NOT NULL,
`destination` varchar(80) NOT NULL,
`sku` int(11) NOT NULL,
`sales` decimal(8,2) NOT NULL,
`units` tinyint(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `tdate` (`tdate`),
KEY `datenat` (`tdate`,`nationality_id`),
KEY `dest` (`destination`),
KEY `txnid` (`txn_id`),
KEY `cov` (`sku`,`nationality_id`,`tdate`)
) ENGINE=InnoDB AUTO_INCREMENT=3236519 DEFAULT CHARSET=utf8;
Unforutnately, this actually increased the query time quite vastly (it took 100 seconds) and here is the explain. Note I have also removed the join on nationalities in the subquery as it was redundant.
Query
SELECT `products`.`brand`, COUNT(DISTINCT transaction_data.txn_id) AS numOrders
FROM `products`
INNER JOIN `transaction_data` ON `transaction_data`.sku=`products`.sku
INNER JOIN `nationalities` ON `transaction_data`.`nationality_id`=`nationalities`.`nationality_id`
INNER JOIN (SELECT DISTINCT `transaction_data`.`txn_id`
FROM `transaction_data`
INNER JOIN `products` USING (sku)
WHERE brand = '<brand>') AS tmp_txns ON tmp_txns.txn_id=`transaction_data`.txn_id
WHERE brand <> '<brand>' AND
tdate >= '2014-01-01'
AND tdate < '2014-01-01' + INTERVAL 1 YEAR
GROUP BY brand
ORDER BY numOrders DESC, brand ASC
LIMIT 10
I don't understand how it's using less rows but taking way longer.
I have also set my innodb_buffer_pool_size to a low "60M" (as per RickJames suggestion for a t2.micro amazon RDS).
Also, to add to previous. Unfortunately setting up summary tables isn't a great idea. While it is correct that they re suitable for my use. I can't guarantee that a user in the future won't try to add data either non-sequentially or even try to swap data out.
Edit
Further to Rick James' answer below
W.O.W - Hit the nail on the head with the date range in the inner query. I was basically running the wrong query the whole time (too many other =brand results). It now runs in 2.4 seconds!
id
= auto incremement, integer
txn_id
= non-unique 'basket' identifier.
'1', '7662_20120101_PC 672_1_12:25:00',
'2', '7662_20120101_PC 672_1_12:25:00',
'3', '7662_20120101_PC 672_1_12:25:00',
'4', '7660_20120101_KL 867_1_12:23:00',
'5', '7662_20120101_PC 672_1_12:25:00',
'6', '7660_20120101_KL 867_1_12:23:00',
'7', '7658_20120101_KL 1211_1_12:12:0', // only sku in basket