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: 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: 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: 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: 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: 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: 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, 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
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: 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
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
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 columns
from tableA
where exists (
select 1
from tableB
where tableB.myCol = tableA.myCol
)
or
select columns 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
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
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 columns
from tableA
where exists (
select 1
from tableB
where tableB.myCol = tableA.myCol
)
or
select columns 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
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
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
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
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
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
