With the efficiency comment below, I was using the strategy that I'd prefer
to wait for 4.1's impending (?) release rather than rewrite SQL in a
workaround way. It's a tradeoff based on our specific project and relating
to time factors, etc, and the amount of SQL I'd have to rewrite.
Yes i'm sure the hurdle could be overcome but I'd rather hold back on MySql
support until subselect functionality is ready.
Typically, database vendors seem to recommend using EXISTS because the query
returns on finding the first row that meets whatever the subselect criteria
Do those people working on 4.1 anticipate the performance of the MySql sub
select functionality to be better than an equivalent query written as a
join? I guess any answer to this should also include consideration of
sometimes putting DISTINCT in the select statement.
Thanks a lot,
----- Original Message -----
From: "Michael T. Babcock" <mbabcock@stripped>
To: "Greg Matthews" <greg55@stripped>
Sent: Monday, November 11, 2002 11:16 AM
Subject: Re: MySql 4.1 Sub Selects
> Greg Matthews wrote:
> >clause) instead of EXISTS -- seems like a "tail wagging the dog"
> >Isn't EXISTS a lot more efficient than an inner join?
> Well, its more efficient if it exists, I guess ... but if it doesn't
> exist on your platform (MySQL), then its pretty inefficient, really.
> >We're going to offering the application on Oracle and so I wouldn't like
> >de-optimize the SQL just so it works on mysql 4.0 if 4.1 will be out
> >a few months.
> If you're writing OO code, you might be able to easily flag your objects
> as to whether to use one or the other query based on the underlying
> database system (queried at run-time).
> Michael T. Babcock
> C.T.O., FibreSpeed Ltd.