Re: How can I improve this table and query

[Adam Copley]

I already had a composite index on products(sku, brand) but have added a single index on brand anyway and the results are: 10.5 sec, 9.3 sec. So a great improvement, here's the explain:

'1', 'SIMPLE', 'products', 'ref', 'skubrand,brand', 'brand', '123', 'const', '23', 'Using index condition; Using where; Using temporary; Using filesort; Start temporary'
'1', 'SIMPLE', 'transaction_data', 'ref', 'txnid,cov', 'cov', '5', '2_sar.products.sku', '647', 'Using where'
'1', 'SIMPLE', 'transaction_data', 'ref', 'txnid,cov,period,natperiod', 'txnid', '99', '2_sar.transaction_data.txn_id', '1', 'Using where; End temporary'
'1', 'SIMPLE', 'nationalities', 'ref', 'PRIMARY,natv', 'natv', '4', '2_sar.transaction_data.nationality_id', '1', 'Using where; Using index'
'1', 'SIMPLE', 'products', 'ref', 'skubrand,brand', 'skubrand', '5', '2_sar.transaction_data.sku', '1', 'Using where; Using index'

Adding the index on brand on its own did a world of good! Thanks...

...In terms of an even smaller result set:
Would partitioning the table by year be a suitable way to retrieve a smaller result set. Surely if I do that, then the query will just hit the correct partition reducing the table scan by almost 2/3rds.

If so, what is the correct way to partition this table through workbench? i.e. the function to use and the conditions to partition by year, given my schema.