MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query - delay 20 segs
Posted by: Rick James
Date: August 31, 2008 11:31PM

Classic problem -- join two tables, WHERE clause hits both of them, and to top it off, you are ordering by something else and doing LIMIT.

Currently it is probably doing
1. table scan on one table, filtering out some rows as it goes
2. reaching into the other table one row at a time, filtering there
3. sorting the intermediate set (rather large)
4. delivering 5 rows.

sector.expired is usually '0', right? Suggest you delete expired rows (or move them to another table). This, by itself won't really help much.

The is a feeble hope that indexing on the converted price might let it search only a part "product". But the computations get in the way. If you could add another column, computed as price*currency, and index it, this might help.

Options: ReplyQuote


Subject
Views
Written By
Posted
4932
August 04, 2008 06:25AM
2462
August 04, 2008 06:26AM
Re: Query - delay 20 segs
2454
August 31, 2008 11:31PM


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.