How can I improve this table and query
I have inherited this table of POS transaction level data. In my opinion it is terrible. There are 3.5 million rows, which span ~4 years, with a distribution of anywhere between 1 result and 100k results for things like `sku`, `brand`, `nationality` and `destination`.
There is no primary key, and I am struggling to figure how I can implement one.
here is the `CREATE TABLE`
CREATE TABLE `transaction_data` (
`txn_id` varchar(32) DEFAULT NULL,
`nationality_id` int(3) DEFAULT NULL,
`transaction date` int(8) DEFAULT NULL,
`YrQtr` varchar(6) DEFAULT NULL,
`Yr` int(4) DEFAULT NULL,
`mth` int(2) DEFAULT NULL,
`dy` int(2) DEFAULT NULL,
`destination` varchar(80) DEFAULT NULL,
`sku` int(6) DEFAULT NULL,
`sales` decimal(8,2) DEFAULT NULL,
`units` int(8) DEFAULT NULL,
KEY `txnid` (`txn_id`),
KEY `cov` (`sku`,`nationality_id`,`Yr`,`YrQtr`),
KEY `period` (`Yr`,`YrQtr`,`mth`),
KEY `natperiod` (`nationality_id`,`Yr`,`YrQtr`),
KEY `desperiod` (`destination`,`Yr`,`YrQtr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Here are 4 example rows of data:
**txn_id** - is a non unique weird concatenate of field values, which represent which **basket** the **sku** was purchased in.
In some **transactions**, the **sku** is not unique, as POS reductions are recorded by another row with the same `txn_id` and `sku` but with negative values in sales and units to reflect those.
INSERT INTO `2_sar`.`transaction_data`
(`txn_id`,
`nationality_id`,
`transaction date`,
`YrQtr`,
`Yr`,
`mth`,
`dy`,
`destination`,
`sku`,
`sales`,
`units`)
VALUES
('1_20120113_EZY 1832_3_08:58:00', '143', '20120113', '2012Q1', '2012', '1', '13', 'United Kingdom - Manchstr UK', '30312', '52.00', '1'
), ('1_20120121_OR 367_1_11:21:00', '143', '20120121', '2012Q1', '2012', '1', '21', 'Neth Antills - Curacao', '710028', '3.95', '1'
), ('1_20120203_PC 672_2_13:07:00', '200', '20120203', '2012Q1', '2012', '2', '3', 'Turkey - Istanbul', '1840', '27.95', '1'
), ('1_20120203_PC 672_2_13:07:00', '200', '20120203', '2012Q1', '2012', '2', '3', 'Turkey - Istanbul', '2156', '12.95', '1'
);
;
Here is a query which I run on the data.
This query finds the top 10 brands that are purchased with `<Brand>` (not including `<brand>`) ordered by total number of transactions.
This query took 11.344 seconds to run directly from mysql workbench. The server is a t2.micro AWS RDS Instance, and I usually run the queries from a separate EC2 web server. which I believe is (1GHz, 1GB).
The wildcards in this query represent `<select>` filters on the PHP page which the query is run from. They may or may not be selected.
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)
INNER JOIN `nationalities` USING (nationality_id)
WHERE brand = <Brand>) AS tmp_txns ON tmp_txns.txn_id=`transaction_data`.txn_id
WHERE brand <> <Brand> AND
nationality LIKE '%' AND
Yr = '2014' AND
YrQtr LIKE '%'
GROUP BY brand
ORDER BY numOrders DESC, brand ASC
LIMIT 10
And the explain:
1 PRIMARY <derived2> ALL 1295294 Using where; Using temporary; Using filesort
1 PRIMARY transaction_data ref txnid,cov,period,natperiod txnid 99 tmp_txns.txn_id 1 Using where
1 PRIMARY nationalities eq_ref PRIMARY,natv PRIMARY 4 2_sar.transaction_data.nationality_id 1 Using where
1 PRIMARY products ref skubrand skubrand 5 2_sar.transaction_data.sku 1 Using where; Using index
2 DERIVED products index skubrand skubrand 128 2669 Using where; Using index; Using temporary
2 DERIVED transaction_data ref txnid,cov,natperiod cov 5 2_sar.products.sku 647 Using index condition
2 DERIVED nationalities eq_ref PRIMARY,natv PRIMARY 4 2_sar.transaction_data.nationality_id 1 Using index
Here are create table statements for the reference tables:
CREATE TABLE `nationalities` (
`nationality_id` int(3) NOT NULL,
`nationality` varchar(14) DEFAULT NULL,
PRIMARY KEY (`nationality_id`),
KEY `natv` (`nationality_id`,`nationality`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `products` (
`sku` int(6) DEFAULT NULL,
`category` varchar(40) DEFAULT NULL,
`subcategory` varchar(40) DEFAULT NULL,
`brand` varchar(40) DEFAULT NULL,
`product name` varchar(50) DEFAULT NULL,
`pack size` decimal(8,2) DEFAULT NULL,
`rk_group` varchar(20) DEFAULT NULL,
`rk_category` varchar(30) DEFAULT NULL,
KEY `skubrand` (`sku`,`brand`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
**My Thoughts So Far**
The `txn_id` is almost guaranteed to bring poor performance, not only due to the fact it's non-unique, but also because it's a string rather than an int. But I need some sort of non-unique identifier for the basket.
I think my `indexes` are okay. All my queries use them, and obviously some `indexes` are specific to certain queries where a single composite `index` won't serve the different `where` clauses in queries.