MySQL Forums
Forum List  »  Optimizer & Parser

Re: Issues with Sub-selects and performance on Win 2003
Posted by: A. Weh
Date: November 12, 2004 05:49AM

These are fairly simple subselects that should work quite fast.
To get good performance you need the following:

1. The draftid must be the same datatype in any of the used tables and must have the same lenght (e.g. in CHAR columns).
2. There must be an index on this column in any of the used tables

If this is the case the optimizer will use the indices to speed the query (regardless if it is a subselect or a join)

Especially condition 1 is more restrictive than in other database systems, so this might be the reason for the bottleneck. You find more details on this in the dokumentation.
BTW, the names of the columns must be identical too (which is the case in your example, but someone might struggle about ist).

Using the EXPLAIN statement will reveale in detail what the optimizer does.

Good luck !

A. Weh

Options: ReplyQuote

Written By
Re: Issues with Sub-selects and performance on Win 2003
November 12, 2004 05:49AM

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.