Re: RIGHT JOIN better than INNER JOIN?
On 3/21/06, Robert DiFalco wrote: I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If your benchmark shows it it must be true :) But is it a significant difference? Over how many test runs? And is it worth the risk that for some join it is actually much slower. If so, it this peculiar to MySQL or would this be true with almost all database servers? Hard to tell. If you want a generic answer the way to go wouls be to start digging in the MySQL sourcecode to find out why it is faster. Once you know that, you can extrapolate to other databases. The only mechanism I can imagine is that you are constraining the planner so for a join between N tables you go from N! plans to (N-1)! plans which will save you a miniscule amount on the planning time for a small join, and somwhat more on a big join. (Does the MySQL planner do an exhaustive search?) The downside is that the plan might be significantly worse so you can loose big time in the executor. Especially considering the semantic difference between both syntaxes I would never use this as a way to constrain the planner. Jochem
Re: RIGHT JOIN better than INNER JOIN?
I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. Use the join that is appropriate for your query, do not use a different one. If speed isn't OK, then bug the guys that do the implementation, but don't start changing the query that _might_ return a different result if, for example, someone else starts working at the application and figures hey, this is a right join, so it's optional etc ... Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RIGHT JOIN better than INNER JOIN?
- Original Message - From: Jochem van Dieten [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, March 21, 2006 3:53 AM Subject: Re: RIGHT JOIN better than INNER JOIN? On 3/21/06, Robert DiFalco wrote: I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If your benchmark shows it it must be true :) But is it a significant difference? Over how many test runs? And is it worth the risk that for some join it is actually much slower. If so, it this peculiar to MySQL or would this be true with almost all database servers? Hard to tell. If you want a generic answer the way to go wouls be to start digging in the MySQL sourcecode to find out why it is faster. Once you know that, you can extrapolate to other databases. No, you can't. Other databases use different optimizers, i.e. different algorithms to choose the access path. Even if you inspected 100 or 1000 or 1,000,000 Fords and found every one of them to have rear-wheel drive, it wouldn't guarantee that BMWs or Hondas had rear-wheel drive, they might have front-wheel drive or all-wheel drive. The specific things you learn about MySQL's optimization technique cannot be extrapolated to other databases unless they are using the same optimization techniques. The only mechanism I can imagine is that you are constraining the planner so for a join between N tables you go from N! plans to (N-1)! plans which will save you a miniscule amount on the planning time for a small join, and somwhat more on a big join. (Does the MySQL planner do an exhaustive search?) The downside is that the plan might be significantly worse so you can loose big time in the executor. Especially considering the semantic difference between both syntaxes I would never use this as a way to constrain the planner. Jochem -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RIGHT JOIN better than INNER JOIN?
Robert, A RIGHT JOIN would potentially return more results than an INNER JOIN. I do not see how it could be, in the absolute, be faster. How large were the tables and how did you do your testing? What platform did you use and were you InnoDB or MyISAM? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Monday, March 20, 2006 5:12 PM To: mysql@lists.mysql.com Subject: RIGHT JOIN better than INNER JOIN? I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RIGHT JOIN better than INNER JOIN?
Martjin, Of course one should use the right JOIN for the job. But let me ask you, which join would you use here? You have a table called Descriptors, it has a field called nameID which is a unique key that relates to a Names table made up of a unique identity and a VARCHAR name. I think most people would write a simple query like this: SELECT desc.fields, names.name FROM desc JOIN names ON desc.nameId = names.Id ORDER BY names.name However, it this really correct? Every descriptor has a record in names, so it could be equivalently written as: SELECT desc.fields, names.name FROM desc RIGHT JOIN names ON desc.nameId = names.Id ORDER BY names.name My guess is that most people conventionally write the first query. R. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 21, 2006 1:01 AM To: mysql@lists.mysql.com Subject: Re: RIGHT JOIN better than INNER JOIN? I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. Use the join that is appropriate for your query, do not use a different one. If speed isn't OK, then bug the guys that do the implementation, but don't start changing the query that _might_ return a different result if, for example, someone else starts working at the application and figures hey, this is a right join, so it's optional etc ... Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RIGHT JOIN better than INNER JOIN?
Robert, Of course one should use the right JOIN for the job. But let me ask you, which join would you use here? You have a table called Descriptors, it has a field called nameID which is a unique key that relates to a Names table made up of a unique identity and a VARCHAR name. I think most people would write a simple query like this: SELECT desc.fields, names.name FROM desc JOIN names ON desc.nameId = names.Id ORDER BY names.name However, it this really correct? Every descriptor has a record in names, so it could be equivalently written as: SELECT desc.fields, names.name FROM desc RIGHT JOIN names ON desc.nameId = names.Id ORDER BY names.name My guess is that most people conventionally write the first query. Gee, I wonder why ... This happens to be the query that returns the rows as it should. What happens, if two years from now you didn't document WHY you wrote a right join query instead of an inner join and someone figures out that this could return nulls for a result column? If you start using the wrong joins, you will make things harder on yourself and others. As I said: if performance isn't satisfactory (which sounds a bit strange for this situation), then try to solve that. Either by using different index/buffer/caching strategies or by complaining to the people who created the database system in the first place. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RIGHT JOIN better than INNER JOIN?
For me the argument is a little pedantic. The contract of the descriptor table is that it must reference a name; there is code and constraints to enforce this. I am happy to have the query return nulls to indicate a programming error that can be quickly addressed. _If_ (after buffer tuning et al) a RIGHT JOIN still provides a substantial performance improvement over a FULL JOIN in this case, my customers would want me to provide that rather than have me tell them it is an inappropriate join or that I am asking the database server developers to improve their query optimizer. I wasn't really looking to get into a philosophical debate on correctness so let me restate my question a little better. Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially out perform a FULL JOIN in those cases where the results would be identical? It is a little difficult to test query performance empirically since performance will change as different indices are swapped in and out of memory buffers and such (although I have turned query caching off), but it appears that for a table with 1-2 million rows a query similar to what I posted here was faster with a RIGHT JOIN. R. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 21, 2006 9:43 AM To: mysql@lists.mysql.com Subject: Re: RIGHT JOIN better than INNER JOIN? Robert, Of course one should use the right JOIN for the job. But let me ask you, which join would you use here? You have a table called Descriptors, it has a field called nameID which is a unique key that relates to a Names table made up of a unique identity and a VARCHAR name. I think most people would write a simple query like this: SELECT desc.fields, names.name FROM desc JOIN names ON desc.nameId = names.Id ORDER BY names.name However, it this really correct? Every descriptor has a record in names, so it could be equivalently written as: SELECT desc.fields, names.name FROM desc RIGHT JOIN names ON desc.nameId = names.Id ORDER BY names.name My guess is that most people conventionally write the first query. Gee, I wonder why ... This happens to be the query that returns the rows as it should. What happens, if two years from now you didn't document WHY you wrote a right join query instead of an inner join and someone figures out that this could return nulls for a result column? If you start using the wrong joins, you will make things harder on yourself and others. As I said: if performance isn't satisfactory (which sounds a bit strange for this situation), then try to solve that. Either by using different index/buffer/caching strategies or by complaining to the people who created the database system in the first place. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RIGHT JOIN better than INNER JOIN?
Robert DiFalco wrote: For me the argument is a little pedantic. The contract of the descriptor table is that it must reference a name; there is code and constraints to enforce this. I am happy to have the query return nulls to indicate a programming error that can be quickly addressed. _If_ (after buffer tuning et al) a RIGHT JOIN still provides a substantial performance improvement over a FULL JOIN in this case, my customers would want me to provide that rather than have me tell them it is an inappropriate join or that I am asking the database server developers to improve their query optimizer. I wasn't really looking to get into a philosophical debate on correctness so let me restate my question a little better. Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially out perform a FULL JOIN in those cases where the results would be identical? It is a little difficult to test query performance empirically since performance will change as different indices are swapped in and out of memory buffers and such (although I have turned query caching off), but it appears that for a table with 1-2 million rows a query similar to what I posted here was faster with a RIGHT JOIN. You have not given enough information to even make a guess. Show the create tables for each table, and the output of explain for each query to see what keys are being used. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RIGHT JOIN better than INNER JOIN?
Robert, Your restatement of your original question uses FULL JOIN as if it means the same things as INNER JOIN: that's simply not correct. A full join contains the results of an inner join PLUS the orphan rows from the right-hand table in the join PLUS the orphan rows from the left-hand table in the join. Furthermore, the last time I checked, which was probably at least a year ago now, MySQL didn't support a full join. -- Rhino - Original Message - From: Robert DiFalco [EMAIL PROTECTED] To: Martijn Tonies [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Tuesday, March 21, 2006 2:04 PM Subject: RE: RIGHT JOIN better than INNER JOIN? For me the argument is a little pedantic. The contract of the descriptor table is that it must reference a name; there is code and constraints to enforce this. I am happy to have the query return nulls to indicate a programming error that can be quickly addressed. _If_ (after buffer tuning et al) a RIGHT JOIN still provides a substantial performance improvement over a FULL JOIN in this case, my customers would want me to provide that rather than have me tell them it is an inappropriate join or that I am asking the database server developers to improve their query optimizer. I wasn't really looking to get into a philosophical debate on correctness so let me restate my question a little better. Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially out perform a FULL JOIN in those cases where the results would be identical? It is a little difficult to test query performance empirically since performance will change as different indices are swapped in and out of memory buffers and such (although I have turned query caching off), but it appears that for a table with 1-2 million rows a query similar to what I posted here was faster with a RIGHT JOIN. R. -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 21, 2006 9:43 AM To: mysql@lists.mysql.com Subject: Re: RIGHT JOIN better than INNER JOIN? Robert, Of course one should use the right JOIN for the job. But let me ask you, which join would you use here? You have a table called Descriptors, it has a field called nameID which is a unique key that relates to a Names table made up of a unique identity and a VARCHAR name. I think most people would write a simple query like this: SELECT desc.fields, names.name FROM desc JOIN names ON desc.nameId = names.Id ORDER BY names.name However, it this really correct? Every descriptor has a record in names, so it could be equivalently written as: SELECT desc.fields, names.name FROM desc RIGHT JOIN names ON desc.nameId = names.Id ORDER BY names.name My guess is that most people conventionally write the first query. Gee, I wonder why ... This happens to be the query that returns the rows as it should. What happens, if two years from now you didn't document WHY you wrote a right join query instead of an inner join and someone figures out that this could return nulls for a result column? If you start using the wrong joins, you will make things harder on yourself and others. As I said: if performance isn't satisfactory (which sounds a bit strange for this situation), then try to solve that. Either by using different index/buffer/caching strategies or by complaining to the people who created the database system in the first place. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.6/286 - Release Date: 20/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RIGHT JOIN better than INNER JOIN?
I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RIGHT JOIN better than INNER JOIN?
The only relational databases I've ever used to any significant extent are MySQL and DB2. I've used DB2 for a lot longer than MySQL and on most of the platforms on which it runs over various versions. As far as I'm concerned, the answer to your questions, at least as far as DB2 goes, is: it depends. It depends on a host of factors. In no particular order, these factors include: - which version of DB2 you are using - what hardware you are running on - how you write your SQL - whether the data is properly clustered - whether the tables and indexes have been reorganized in a timely fashion - etc. etc. You simply can't make a categorical statement that a right join will perform better than an inner join - or vice versa - in every case in DB2. All versions of DB2 use a cost-based optimizer that makes great efforts to give the optimum access path (and therefore optimum performance) for each query. A lot of very smart people have worked on the design of that optimizer over the years - I've met some of them - but, as good as the DB2 optimizer is, it can still make inappropriate decisions. This happens when you don't do routine maintenance like reorganizing tables and the RUNSTATS utility but the way you write (or mis-write) your SQL can also affect your access path and therefore your performance. This unpredictability may sound like a bad thing but it is often a very good thing since the optimizer has many tricks and shortcuts. It will often rewrite a poorly-written query to improve its performance. You may be able to find more categorical answers for the other major databases, like Oracle, since they tend to use different optimizer designs. The only way to be really sure though is to do a proper benchmark for all the platforms and configurations that interest you. -- Rhino - Original Message - From: Robert DiFalco [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 20, 2006 7:11 PM Subject: RIGHT JOIN better than INNER JOIN? I apologize if this is a naive question but it appears through my testing that a RIGHT JOIN may out perform an INNER JOIN in those cases where they would produce identical result sets. i.e. there are no keys in the left table that do not exist in the right table. Is this true? If so, it this peculiar to MySQL or would this be true with almost all database servers? i.e. Oracle, DB2, MSSQL, etc. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.2.5/284 - Release Date: 17/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
