Re: RE: MySql 4.1 Sub Selects and not stored procedures
the EXISTS logic is ugly in as sql statement. does that count? seems like ugly should count. > > hey, can i hijack my message back?...this thread is about the performance of > subselects, not stored procedures. go write your own message :-)... > > sothe 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 > > > > -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.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
Re: RE: MySql 4.1 Sub Selects and not stored procedures
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 :-)...
>
> sothe 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, pl
Re: MySql 4.1 Sub Selects and not stored procedures
In the last episode (Nov 11), Greg Matthews said: > > hey, can i hijack my message back?...this thread is about the performance of > subselects, not stored procedures. go write your own message :-)... > > sothe 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. I would expect that the explain plans for both queries would end up being identical, so the only difference is whichever you think is more readable. I don't remember reading any Oracle documentation recommending subselects. -- Dan Nelson [EMAIL PROTECTED] - 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
Re: MySql 4.1 Sub Selects
Dan Rossi wrote: 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 Don't forget that with MySQL, you can link in external procedures though ... its not quite the same, but it gets you some interesting functionality. QUERY. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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
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 :-)...
sothe 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
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 :-)... sothe 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
Re: re[2]: MySql 4.1 Sub Selects
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 11 November 2002 13:03, Richard Morton wrote: > according to this link... 4.1 will have stored procedures > > http://www.mysql.com/products/mysql-4.0/index.html Oops, that page needs to be updated. Stored procedures will come with MySQL 5.0, not 4.1. Sorry about that. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Lenz Grimmer <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany <___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) iD8DBQE9z6DnSVDhKrJykfIRAqMoAJ9yNzyAioleOV247MJx9tWRg+3+NwCeNqsr fQgR8EHornlWhO1cAXEMwpc= =i122 -END PGP SIGNATURE- - 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
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
re[2]: MySql 4.1 Sub Selects
Hi according to this link... 4.1 will have stored procedures http://www.mysql.com/products/mysql-4.0/index.html Rich >> 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 >> is. >> 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, >> Greg. >> - Original Message - >> From: "Michael T. Babcock" <[EMAIL PROTECTED]> >> To: "Greg Matthews" <[EMAIL PROTECTED]> >> Cc: <[EMAIL PROTECTED]> >> 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" >> strategy. >> > >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 >> to >> > >de-optimize the SQL just so it works on mysql 4.0 if 4.1 will be out >> within >> > >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. >> > http://www.fibrespeed.net/~mbabcock >> > >> > >> - >> 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 Regards, Richard Morton Quantum Communications +44 (0) 7899 750400 [EMAIL PROTECTED] www.quantum-comms.com - This email, its content and any attachments is PRIVATE AND CONFIDENTIAL to Quantum Communications and is intended for the recipient(s) named above only. If received in error please notify the sender and destroy the original message and attachments. Emails may be monitored or recorded. It is the responsibility of the recipient to ensure that the e-mail and/or any attachment is virus free. The views expressed by the author may not reflect the views of Quantum Communications Limited. - - 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
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
RE: MySql 4.1 Sub Selects
will 4.1 hopefully have stored procedure functionality ? >= Original Message From "Greg Matthews" <[EMAIL PROTECTED]> = >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 >is. > >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, >Greg. > >- Original Message - >From: "Michael T. Babcock" <[EMAIL PROTECTED]> >To: "Greg Matthews" <[EMAIL PROTECTED]> >Cc: <[EMAIL PROTECTED]> >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" >strategy. >> >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 >to >> >de-optimize the SQL just so it works on mysql 4.0 if 4.1 will be out >within >> >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. >> http://www.fibrespeed.net/~mbabcock >> >> > > >- >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
Re: MySql 4.1 Sub Selects
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 is. 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, Greg. - Original Message - From: "Michael T. Babcock" <[EMAIL PROTECTED]> To: "Greg Matthews" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> 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" strategy. > >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 to > >de-optimize the SQL just so it works on mysql 4.0 if 4.1 will be out within > >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. > http://www.fibrespeed.net/~mbabcock > > - 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
Re: MySql 4.1 Sub Selects
Greg Matthews wrote: clause) instead of EXISTS -- seems like a "tail wagging the dog" strategy. 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 to de-optimize the SQL just so it works on mysql 4.0 if 4.1 will be out within 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. http://www.fibrespeed.net/~mbabcock - 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
Re: MySql 4.1 Sub Selects
"Why don't you simply convert these queries to ordinary JOINs?"
...because we've already got a 90% finished application (75 tables/150 jsp
pages).
I was toying around with the idea last night of using a JOIN instead of
EXISTS but i'd have to change the way the persistence layer works. I'm not
sure that it's a good idea to use joins (often requiring the DISTINCT
clause) instead of EXISTS -- seems like a "tail wagging the dog" strategy.
Isn't EXISTS a lot more efficient than an inner join?
We're going to offering the application on Oracle and so I wouldn't like to
de-optimize the SQL just so it works on mysql 4.0 if 4.1 will be out within
a few months.
Seems like I might be best to check again in a while and see how quickly you
guys have progressed with 4.1.
Cheers,
Greg.
- Original Message -
From: "Harald Fuchs" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, November 10, 2002 11:16 PM
Subject: Re: MySql 4.1 Sub Selects
> In article <014701c288a4$1b1bb550$0100a8c0@dev>,
> "Greg Matthews" <[EMAIL PROTECTED]> writes:
>
> > Your call. If a 4.1 binary was available then I'd spend time testing it
with
> > a J2EE app under JBoss on Windows XP -- otherwise i'll just have to
wait.
>
> > 95% of the subselects I do are simple correlated subqueries like this:
>
> > select
> > from tableA
> > where exists (
> > select 1
> > from tableB
> > where tableB.myCol = tableA.myCol
> > )
>
> > or
>
> > select from tableA
> > where exists (
> > select 1
> > from tableB
> > where tableB.myCol = tableA.myCol
> > and tableB.someCol in ('A','B','C')
> > )
>
> Why don't you simply convert these queries to ordinary JOINs?
> Me thinks that's easier than experimenting with unoptimized pre-alpha
> code.
>
> [Filter fodder: SQL query]
>
> -
> 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
Re: MySql 4.1 Sub Selects
On Sun, Nov 10, 2002 at 09:29:52PM +1100, Greg Matthews wrote: > Jeremy, > > I'm not a C/C++ coder so couldn't really help unless a binary was > available. > > At the same time, I understand that you'd be wasting your time building > binaries unless you were happy that the code was at a certain level of > completion (with regard to 4.1 specific features). Actually, I alredy build binaries of 3.23, 4.0, and 4.1 nightly on Linux and FreeBSD using MySQL AB's development code. I've automated it, so it takes none of my time now. I just don't generally share them outside of work. :-) > Your call. If a 4.1 binary was available then I'd spend time testing > it with a J2EE app under JBoss on Windows XP -- otherwise i'll just > have to wait. Err, Windows binaries are a whole different matter. I don't have the tools or knowledge necessary to build 'em. Perhaps someone else on the list does... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 3.23.51: up 96 days, processed 2,007,837,623 queries (241/sec. avg) - 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
Re: MySql 4.1 Sub Selects
Jeremy,
I'm not a C/C++ coder so couldn't really help unless a binary was available.
At the same time, I understand that you'd be wasting your time building
binaries unless you were happy that the code was at a certain level of
completion (with regard to 4.1 specific features).
Your call. If a 4.1 binary was available then I'd spend time testing it with
a J2EE app under JBoss on Windows XP -- otherwise i'll just have to wait.
95% of the subselects I do are simple correlated subqueries like this:
select
from tableA
where exists (
select 1
from tableB
where tableB.myCol = tableA.myCol
)
or
select from tableA
where exists (
select 1
from tableB
where tableB.myCol = tableA.myCol
and tableB.someCol in ('A','B','C')
)
Regards,
Greg.
- Original Message -
From: "Jeremy Zawodny" <[EMAIL PROTECTED]>
To: "Greg Matthews" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, November 10, 2002 9:00 PM
Subject: Re: MySql 4.1 Sub Selects
> On Sun, Nov 10, 2002 at 05:51:43PM +1100, Greg Matthews wrote:
> >
> > It would be a huge help to get hold of MySql 4.1.
> >
> > What's "shaken out" mean? Is 4.1 still mid-development, or is it in
> > alpha, more or less finished, and needs debugging?
> >
> > Is there anyway soon that someone could build and release some
> > binaries? I'd be happy to do testing if 4.1 is in a semi-usable
> > state.
>
> I can build them and make 'em available. But I won't support them.
> If they crash for you, they crash. But they'd certainly be helpful
> for testing new features, if that's what you're intending.
> --
> Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo!
> <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/
>
> MySQL 3.23.51: up 96 days, processed 2,007,545,749 queries (241/sec. avg)
>
> -
> 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
Re: MySql 4.1 Sub Selects
Hi, It remains some bugs in MySQL 4.1 subselect implementation which are on the way to be resolve. (take a look at the [EMAIL PROTECTED]) Also, support for IN subselect are not yet available (you can find the code in reading the bk commit, but it's not yet push). Lastly, subselect are not yet optimised (it will be started after finishing main subselect constructions). Regards, Jocelyn - Original Message - From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: "Greg Matthews" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, November 10, 2002 10:00 AM Subject: Re: MySql 4.1 Sub Selects > On Sun, Nov 10, 2002 at 05:51:43PM +1100, Greg Matthews wrote: > > > > It would be a huge help to get hold of MySql 4.1. > > > > What's "shaken out" mean? Is 4.1 still mid-development, or is it in > > alpha, more or less finished, and needs debugging? > > > > Is there anyway soon that someone could build and release some > > binaries? I'd be happy to do testing if 4.1 is in a semi-usable > > state. > > I can build them and make 'em available. But I won't support them. > If they crash for you, they crash. But they'd certainly be helpful > for testing new features, if that's what you're intending. > -- > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! - 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
Re: MySql 4.1 Sub Selects
On Sun, Nov 10, 2002 at 05:51:43PM +1100, Greg Matthews wrote: > > It would be a huge help to get hold of MySql 4.1. > > What's "shaken out" mean? Is 4.1 still mid-development, or is it in > alpha, more or less finished, and needs debugging? > > Is there anyway soon that someone could build and release some > binaries? I'd be happy to do testing if 4.1 is in a semi-usable > state. I can build them and make 'em available. But I won't support them. If they crash for you, they crash. But they'd certainly be helpful for testing new features, if that's what you're intending. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 3.23.51: up 96 days, processed 2,007,545,749 queries (241/sec. avg) - 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
Re: MySql 4.1 Sub Selects
It would be a huge help to get hold of MySql 4.1. What's "shaken out" mean? Is 4.1 still mid-development, or is it in alpha, more or less finished, and needs debugging? Is there anyway soon that someone could build and release some binaries? I'd be happy to do testing if 4.1 is in a semi-usable state. Greg. - Original Message - From: "Jeremy Zawodny" <[EMAIL PROTECTED]> To: "Greg Matthews" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, November 10, 2002 4:21 PM Subject: Re: MySql 4.1 Sub Selects > On Sun, Nov 10, 2002 at 03:45:04PM +1100, Greg Matthews wrote: > > All, > > > > Anyone have any rough idea when 4.1 (with SubSelects) is due to be released? > > Well, if you pull the source code, it's there. But I suspect it'll be > a few months before you start seeing pre-built binaries available. > There's a lot that needs to be shaken out first--at least that's my > impression so far. > > Jeremy > -- > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ > > MySQL 3.23.51: up 95 days, processed 2,004,951,757 queries (242/sec. avg) > - 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
Re: MySql 4.1 Sub Selects
On Sun, Nov 10, 2002 at 03:45:04PM +1100, Greg Matthews wrote: > All, > > Anyone have any rough idea when 4.1 (with SubSelects) is due to be released? Well, if you pull the source code, it's there. But I suspect it'll be a few months before you start seeing pre-built binaries available. There's a lot that needs to be shaken out first--at least that's my impression so far. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 3.23.51: up 95 days, processed 2,004,951,757 queries (242/sec. avg) - 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
MySql 4.1 Sub Selects
All, Anyone have any rough idea when 4.1 (with SubSelects) is due to be released? Thanks, Greg. sql,query - 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
