Re: [PHP] Re: the opposite of a join?
On Thu, 2007-10-04 at 11:56 +1000, Chris wrote: Robert Cummings wrote: On Thu, 2007-10-04 at 11:23 +1000, Chris wrote: Robert Cummings wrote: On Wed, 2007-10-03 at 14:49 -0700, Jim Lucas wrote: This is only from my own personal testing. Mind you that I have only been using PostgreSQL for a year or so. But one problem that I have always ran into with MySQL is that when JOIN'ing tables that have large data sets is a PITA. Were you doing left joins when you experienced those problems? Left joins are usually very fast. If indexed properly of course ;) Yes, but you're not going to get a performance improvement if you use anything else if the table isn't properly indexed. A subselect could win out in terms of performance especially if the table in the subselect is reasonably small (eg all fits into memory). But if it fits in memory then it's probably already in memory for a left join also. Cheers, Rob. -- ... SwarmBuy.com - http://www.swarmbuy.com Leveraging the buying power of the masses! ... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: [PHP-DB] Re: [PHP] Re: the opposite of a join?
left join where item in right table is null "Chris" [EMAIL PROTECTED] 10/3/2007 10:32:01 PM Aleksandar Vojnovic wrote: I would also suggest to limit yourself to things you actually need not to select the whole table.In this case you can't because you're looking for records that exist in one table that don't exist in another.Apart from looking at the whole table in each case how else would you do that?-- Postgresql php tutorialshttp://www.designmagick.com/-- PHP General Mailing List (http://www.php.net/)To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: [PHP-DB] Re: [PHP] Re: the opposite of a join?
John A DAVIS wrote: left join where item in right table is null That's still going to look at all records in both tables: 1) so it can work out if there is a match from table 1 to table 2 2) so it can then remember to display any records that don't have a match I was thinking more that if you have something like this: select * from table1 where id not in (select id from table2); The db might take that and turn it into: select * from table1 where id not in (id1,id2,id3); But it doesn't really matter. Either way you end up with full table or index scans (depending on the db and engine you are using if mysql) of both table1 and table2. myisam tables might just be able to use an index to do this sort of work, innodb will have to do a table scan because it's mvcc (as will postgres and others). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] RE: the opposite of a join?
On 10/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi J, Checkout this, SELECT * FROM tbl_company where id not in (SELECT companyID from tbl_contacts) Brilliant! This is exactly what I was looking for, and is quite logical/readable! Thanks to everyone for the ideas! J No, don't do this! It is a very inefficient way to retrieve the information you are looking for (Use a query analysis tool to check it out yourself, if you want) - if your tables get to any larger size at all, it will start having a noticeable performance impact on your script (not to mention your DB) - let the DB do the hard work and use a LEFT JOIN syntax, the database can optimize that much more efficiently. Only if your DB doesn't support the LEFT JOIN syntax would you want to do the above. -James Regards, Lasitha Alawatta Application Developer Destinations of the World Holding Establishment P O Box: 19950 Dubai, United Arab Emirates ( Ph +971 4 295 8510 (Board) / 1464 (Ext.) 7 Fax +971 4 295 8910 + [EMAIL PROTECTED] -Original Message- From: John Pillion [mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, October 03, 2007 2:21 PM To: php-general@lists.php.net; [EMAIL PROTECTED] Subject: [PHP-DB] the opposite of a join? I have a company table and a contacts table. In the contacts table, there is a field called companyID which is a link to a row in the company table. What is the easiest way to query the company table for all the company rows whose ID is NOT linked to in the contact table? Basically, the opposite of a join? Thanks J DOTW DISCLAIMER: This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately. This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability in connection therewith. This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] RE: the opposite of a join?
I agree with this. Never use a subquery when a join will work. The optimizer with thank you with performance. James Ausmus wrote: On 10/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi J, Checkout this, SELECT * FROM tbl_company where id not in (SELECT companyID from tbl_contacts) Brilliant! This is exactly what I was looking for, and is quite logical/readable! Thanks to everyone for the ideas! J No, don't do this! It is a very inefficient way to retrieve the information you are looking for (Use a query analysis tool to check it out yourself, if you want) - if your tables get to any larger size at all, it will start having a noticeable performance impact on your script (not to mention your DB) - let the DB do the hard work and use a LEFT JOIN syntax, the database can optimize that much more efficiently. Only if your DB doesn't support the LEFT JOIN syntax would you want to do the above. -James Regards, Lasitha Alawatta Application Developer Destinations of the World Holding Establishment P O Box: 19950 Dubai, United Arab Emirates ( Ph +971 4 295 8510 (Board) / 1464 (Ext.) 7 Fax +971 4 295 8910 + [EMAIL PROTECTED] -Original Message- From: John Pillion [mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, October 03, 2007 2:21 PM To: php-general@lists.php.net; [EMAIL PROTECTED] Subject: [PHP-DB] the opposite of a join? I have a company table and a contacts table. In the contacts table, there is a field called companyID which is a link to a row in the company table. What is the easiest way to query the company table for all the company rows whose ID is NOT linked to in the contact table? Basically, the opposite of a join? Thanks J DOTW DISCLAIMER: This e-mail and any attachments are strictly confidential and intended for the addressee only. If you are not the named addressee you must not disclose, copy or take any action in reliance of this transmission and you should notify us as soon as possible. If you have received it in error, please contact the message sender immediately. This e-mail and any attachments are believed to be free from viruses but it is your responsibility to carry out all necessary virus checks and DOTW accepts no liability in connection therewith. This e-mail and all other electronic (including voice) communications from the sender's company are for informational purposes only. No such communication is intended by the sender to constitute either an electronic record or an electronic signature or to constitute any agreement by the sender to conduct a transaction by electronic means. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: the opposite of a join?
Colin Guthrie wrote: Martin Marques wrote: SELECT * FROM company WHERE id NOT IN (SELECT companyID FROM contacts); Not ideal as has been mentioned else where in this thread. Col I think one would have to take into account the DB type being used here. I can have MySQL and PostgreSQL setup and running with the same table structure (well, as close as you can get) configured with two different databases in them. SQL #1 SELECT * FROMcompany WHERE id NOT IN ( SELECT companyID FROMcontacts ); SQL #2 SELECT company.* FROMcompany LEFT JOIN contacts ON ( company.companyID = contacts.companyID ) WHERE contacts.companyID IS NULL Now, both SQL statements will perform relatively the same on either DB's with a small data set. but, if you have a large data set, MySQL will benefit from having the Sub-Query style statement Where-as PostgreSQL will shine with the JOIN command. This is only from my own personal testing. Mind you that I have only been using PostgreSQL for a year or so. But one problem that I have always ran into with MySQL is that when JOIN'ing tables that have large data sets is a PITA. So, if I was running MySQL, I would use SQL #1, but if I were using PostgreSQL, I would use SQL #2 If anybody else has suggestions or comments about performance between MySQL vs. PostgreSQL with regards to similarly formed SQL calls, I would like to hear their experiences. -- Jim Lucas Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them. Twelfth Night, Act II, Scene V by William Shakespeare -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: the opposite of a join?
On Wed, 2007-10-03 at 14:49 -0700, Jim Lucas wrote: This is only from my own personal testing. Mind you that I have only been using PostgreSQL for a year or so. But one problem that I have always ran into with MySQL is that when JOIN'ing tables that have large data sets is a PITA. Were you doing left joins when you experienced those problems? Left joins are usually very fast. So, if I was running MySQL, I would use SQL #1, but if I were using PostgreSQL, I would use SQL #2 I'd use the left join whenever available. Cheers, Rob. -- ... SwarmBuy.com - http://www.swarmbuy.com Leveraging the buying power of the masses! ... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: the opposite of a join?
On Wed, 3 Oct 2007, Robert Cummings wrote: I'd use the left join whenever available. Similarly, I design for the left join whenever possible. -- Greg Donald Cyberfusion Consulting http://cyberfusionconsulting.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: the opposite of a join?
Robert Cummings wrote: On Wed, 2007-10-03 at 14:49 -0700, Jim Lucas wrote: This is only from my own personal testing. Mind you that I have only been using PostgreSQL for a year or so. But one problem that I have always ran into with MySQL is that when JOIN'ing tables that have large data sets is a PITA. Were you doing left joins when you experienced those problems? Left joins are usually very fast. If indexed properly of course ;) -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: the opposite of a join?
On Thu, 2007-10-04 at 11:23 +1000, Chris wrote: Robert Cummings wrote: On Wed, 2007-10-03 at 14:49 -0700, Jim Lucas wrote: This is only from my own personal testing. Mind you that I have only been using PostgreSQL for a year or so. But one problem that I have always ran into with MySQL is that when JOIN'ing tables that have large data sets is a PITA. Were you doing left joins when you experienced those problems? Left joins are usually very fast. If indexed properly of course ;) Yes, but you're not going to get a performance improvement if you use anything else if the table isn't properly indexed. Cheers, Rob. -- ... SwarmBuy.com - http://www.swarmbuy.com Leveraging the buying power of the masses! ... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: the opposite of a join?
Robert Cummings wrote: On Thu, 2007-10-04 at 11:23 +1000, Chris wrote: Robert Cummings wrote: On Wed, 2007-10-03 at 14:49 -0700, Jim Lucas wrote: This is only from my own personal testing. Mind you that I have only been using PostgreSQL for a year or so. But one problem that I have always ran into with MySQL is that when JOIN'ing tables that have large data sets is a PITA. Were you doing left joins when you experienced those problems? Left joins are usually very fast. If indexed properly of course ;) Yes, but you're not going to get a performance improvement if you use anything else if the table isn't properly indexed. A subselect could win out in terms of performance especially if the table in the subselect is reasonably small (eg all fits into memory). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: the opposite of a join?
Robert Cummings wrote: On Wed, 2007-10-03 at 14:49 -0700, Jim Lucas wrote: This is only from my own personal testing. Mind you that I have only been using PostgreSQL for a year or so. But one problem that I have always ran into with MySQL is that when JOIN'ing tables that have large data sets is a PITA. Were you doing left joins when you experienced those problems? Left joins are usually very fast. So, if I was running MySQL, I would use SQL #1, but if I were using PostgreSQL, I would use SQL #2 I'd use the left join whenever available. Cheers, Rob. Honestly, I cannot remember. It was right when I first started with PHP/mysql back in 1999. I think we were using a JOIN (without the LEFT) Which I think the default is an INNER JOIN if I do recall. I really have never played with performance over the past few years. This past year I have been working on a new DB with Call Detail Records for a phone company. On average we have to deal with processing 2 - 4 million records each billing cycle. So, having to work with that amount of CDR's and a couple thousand client records that are associated with them, makes for a good performance test on SQL statements. -- Jim Lucas Perseverance is not a long race; it is many short races one after the other Walter Elliot Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them. Twelfth Night, Act II, Scene V by William Shakespeare -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: the opposite of a join?
I would also suggest to limit yourself to things you actually need not to select the whole table. Aleksandar Jim Lucas wrote: Colin Guthrie wrote: Martin Marques wrote: SELECT * FROM company WHERE id NOT IN (SELECT companyID FROM contacts); Not ideal as has been mentioned else where in this thread. Col I think one would have to take into account the DB type being used here. I can have MySQL and PostgreSQL setup and running with the same table structure (well, as close as you can get) configured with two different databases in them. SQL #1SELECT* FROMcompany WHEREid NOT IN( SELECTcompanyID FROMcontacts ); SQL #2 SELECTcompany.* FROMcompany LEFT JOIN contacts ON( company.companyID = contacts.companyID ) WHEREcontacts.companyID IS NULL Now, both SQL statements will perform relatively the same on either DB's with a small data set. but, if you have a large data set, MySQL will benefit from having the Sub-Query style statement Where-as PostgreSQL will shine with the JOIN command. This is only from my own personal testing. Mind you that I have only been using PostgreSQL for a year or so. But one problem that I have always ran into with MySQL is that when JOIN'ing tables that have large data sets is a PITA. So, if I was running MySQL, I would use SQL #1, but if I were using PostgreSQL, I would use SQL #2 If anybody else has suggestions or comments about performance between MySQL vs. PostgreSQL with regards to similarly formed SQL calls, I would like to hear their experiences. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: [PHP-DB] Re: [PHP] Re: the opposite of a join?
Aleksandar Vojnovic wrote: I would also suggest to limit yourself to things you actually need not to select the whole table. In this case you can't because you're looking for records that exist in one table that don't exist in another. Apart from looking at the whole table in each case how else would you do that? -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: [PHP-DB] Re: [PHP] Re: the opposite of a join?
It seems you missed my point :) if you would need all the data then select them all, but if you need only partial data from the table then you could limit yourself to that specific columns. I doubt everybody need everything all the time. True? Aleksandar Chris wrote: Aleksandar Vojnovic wrote: I would also suggest to limit yourself to things you actually need not to select the whole table. In this case you can't because you're looking for records that exist in one table that don't exist in another. Apart from looking at the whole table in each case how else would you do that? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: [PHP-DB] Re: [PHP] Re: the opposite of a join?
Aleksandar Vojnovic wrote: It seems you missed my point :) if you would need all the data then select them all, but if you need only partial data from the table then you could limit yourself to that specific columns. I doubt everybody need everything all the time. True? Ahh - you meant the select * from table bit ;) My apologies. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
