the question was:
====> Do any MySql coders writing subselects in 4.1 know whether EXISTS will
outperform an equivalent query written as a join. <=====
yes or no ?
greg.
----- Original Message -----
From: "Arthur Fuller" <[EMAIL PROTECTED]>
To: "Greg Matthews" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, November 12, 2002 12:02 AM
Subject: RE: RE: MySql 4.1 Sub Selects and not stored procedures
> I'm not sure where you're getting your information ("Typically, db vendors
> recommend you use an exists clause, not a join when testing for the
presence
> of child data because it's faster"). Not to say that you're incorrect,
just
> that in working for years with MS-SQL and Oracle and before that Sybase, I
> have never seen such advice.
>
> I just opened Query Analyzer to compare two queries similar to yours (in
> MS-SQL).
>
> Table sizes:
> Customers: 50151
> SalesTravellers: 51195
>
> Q1:
> select surname, givenName
> from salestravellers
> where not exists(
> select 1 from customers
> where salestravellers.customerid = customers.customerid)
>
> Q2:
> select salestravellers.surname, salestravellers.givenName
> from salestravellers left join customers
> on salestravellers.customerid = customers.customerid
> where customers.customerid is null
>
> For both queries:
> Rows returned: 2751
> Time for Q1: 0:0:02
>
>
> Arthur
>
> -----Original Message-----
> From: Greg Matthews [mailto:greg55@;ozemail.com.au]
> Sent: Monday, November 11, 2002 7:38 AM
> To: [EMAIL PROTECTED]
> Subject: Re: RE: MySql 4.1 Sub Selects and not stored procedures
>
>
>
> hey, can i hijack my message back?...this thread is about the performance
of
> subselects, not stored procedures. go write your own message :-)...
>
> so....the original question is if someone would be nice enough to answer..
>
> ====> Do any MySql coders writing subselects in 4.1 know whether EXISTS
will
> outperform an equivalent query written as a join. <=====
>
> Typically, db vendors recommend you use an exists clause, not a join when
> testing for the presence of child data because it's faster.
>
> e.g.
>
> 4.1 version (faster?)
> ---------------------
> select person.person_id, person.name from person where exists ( select 1
> from invoice where invoice.paid is null and invoice.person_id =
> person.person_id)
>
> vs
>
> 4.0 version (slower?)
> ----------------------
> select DISTINCT person.person_id, person.person_name from person, invoice
> where person.person_id = invoice.person_id and invoice.paid is null
>
>
> Thanks,
> Greg.
>
> ----- Original Message -----
> From: "Dan Rossi" <[EMAIL PROTECTED]>
> To: "Victoria Reznichenko" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Monday, November 11, 2002 11:04 PM
> Subject: RE: RE: MySql 4.1 Sub Selects
>
>
> > damn , i read it was 4.1 i guess we have to wait a bit then, i wish i
> could
> > program some c ++ to hurry it along a bit, i dont really have access to
> DB's
> > like oracle to learn stored procedure stuff
> >
> > -----Original Message-----
> > From: Victoria Reznichenko [mailto:victoria.reznichenko@;ensita.net]
> > Sent: Monday, November 11, 2002 11:00 PM
> > To: [EMAIL PROTECTED]
> > Subject: re: RE: MySql 4.1 Sub Selects
> >
> >
> > daniel,
> > Monday, November 11, 2002, 3:51:25 AM, you wrote:
> >
> > d> will 4.1 hopefully have stored procedure functionality ?
> >
> > Nope.
> > Stored procedures will be implemented around v5.0
> >
> >
> > --
> > For technical support contracts, goto
https://order.mysql.com/?ref=ensita
> > This email is sponsored by Ensita.net http://www.ensita.net/
> > __ ___ ___ ____ __
> > / |/ /_ __/ __/ __ \/ / Victoria Reznichenko
> > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
> > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
> > <___/ www.mysql.com
> >
> >
> >
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
