MySQL Forums
Forum List  »  Performance

Re: How can I improve this table and query
Posted by: Adam Copley
Date: February 17, 2016 09:03AM

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.

Options: ReplyQuote

Written By
February 17, 2016 04:49AM
Re: How can I improve this table and query
February 17, 2016 09:03AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.