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: 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
