Re: How can I improve this table and query

[Adam Copley]

Hi there, thanks for your reply.

Thanks for spotting the unused join. I ran your version of the query through workbench again. But strangely it took 18 seconds, then 15 seconds to run, despite a better looking explain.

'1', 'SIMPLE', 'nationalities', 'index', 'PRIMARY,natv', 'natv', '49', NULL, '229', 'Using where; Using index; Using temporary; Using filesort'
'1', 'SIMPLE', 'transaction_data', 'ref', 'txnid,cov,period,natperiod', 'natperiod', '10', '2_sar.nationalities.nationality_id,const', '1608', 'Using index condition; Using where'
'1', 'SIMPLE', 'products', 'ref', 'skubrand', 'skubrand', '5', '2_sar.transaction_data.sku', '1', 'Using where; Using index'
'1', 'SIMPLE', 'transaction_data', 'ref', 'txnid,cov', 'txnid', '99', '2_sar.transaction_data.txn_id', '1', 'Using where'
'1', 'SIMPLE', 'products', 'ref', 'skubrand', 'skubrand', '128', '2_sar.transaction_data.sku,const', '1', 'Using where; Using index; FirstMatch(products)'