List:General Discussion« Previous MessageNext Message »
From:Arthur Fuller Date:November 11 2002 2:02pm
Subject:RE: RE: MySql 4.1 Sub Selects and not stored procedures
View as plain text  
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@stripped]
Sent: Monday, November 11, 2002 7:38 AM
To: mysql@stripped
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" <daniel@stripped>
To: "Victoria Reznichenko" <victoria.reznichenko@stripped>;
<mysql@stripped>
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@stripped]
> Sent: Monday, November 11, 2002 11:00 PM
> To: mysql@stripped
> 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
>  / /|_/ / // /\ \/ /_/ / /__   Victoria.Reznichenko@stripped
> /_/  /_/\_, /___/\___\_\___/   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 <mysql-thread124619@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-daniel=electroteque.org@stripped>
> 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 <mysql-thread124626@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-greg55=ozemail.com.au@stripped>
> 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 <mysql-thread124629@stripped>
To unsubscribe, e-mail <mysql-unsubscribe-afuller=etsys.com@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



Thread
MySql 4.1 Sub SelectsGreg Matthews10 Nov
  • Re: MySql 4.1 Sub SelectsJeremy Zawodny10 Nov
  • Re: MySql 4.1 Sub SelectsGreg Matthews10 Nov
    • Re: MySql 4.1 Sub SelectsJeremy Zawodny10 Nov
  • Re: MySql 4.1 Sub SelectsJocelyn Fournier10 Nov
  • Re: MySql 4.1 Sub SelectsGreg Matthews10 Nov
    • Re: MySql 4.1 Sub SelectsJeremy Zawodny10 Nov
  • Re: MySql 4.1 Sub SelectsHarald Fuchs10 Nov
  • Re: MySql 4.1 Sub SelectsGreg Matthews10 Nov
  • Re: MySql 4.1 Sub SelectsMichael T. Babcock11 Nov
  • Re: MySql 4.1 Sub SelectsGreg Matthews11 Nov
RE: MySql 4.1 Sub Selectsdaniel11 Nov
  • re: RE: MySql 4.1 Sub SelectsVictoria Reznichenko11 Nov
    • RE: RE: MySql 4.1 Sub SelectsDan Rossi11 Nov
      • Re: RE: MySql 4.1 Sub Selects and not stored proceduresGreg Matthews11 Nov
        • RE: RE: MySql 4.1 Sub Selects and not stored proceduresArthur Fuller11 Nov
          • Re: RE: MySql 4.1 Sub Selects and not stored proceduresGreg Matthews11 Nov
        • Re: MySql 4.1 Sub Selects and not stored proceduresDan Nelson11 Nov
        • Re: RE: MySql 4.1 Sub Selects and not stored proceduresJohn Ragan11 Nov
      • Re: MySql 4.1 Sub SelectsMichael T. Babcock11 Nov
re[2]: MySql 4.1 Sub SelectsRichard Morton11 Nov
  • Re: re[2]: MySql 4.1 Sub SelectsLenz Grimmer11 Nov