Re: Left Join Help
the thing with JOINs are you gotta JOIN a table ON another table where something matches something else (in most cases). I tried to clean this up a bit but im rather new to mysql. SELECT DISTINCT (td.td_id), td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name, art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1, tv.CITY as ven_city, tv.STATE ven_state, tv.ZIPCODE as ven_zip, tv.COUNTRY, tv.WEBSITE as ven_url, tvc.SIZE as capacity, tvage.TYPE as age, tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td INNER JOIN tbl_ARTST as art ON? INNER JOIN artist_tourdate artd ON? INNER JOIN tbl_VENUES tv ON? INNER JOIN tbl_VENUE_CAPACITY tvc ON? INNER JOIN tbl_VENUE_AGE_XREF tvax ON? INNER JOIN tbl_VENUE_AGES tvage ON? LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 iHTH, On 6/23/06, Peter Brawley [EMAIL PROTECTED] wrote: Paul, SELECT ... FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc , tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 FROM ... tbl_VENUE_CAPACITY tvc, ... LEFT JOIN tbl_VENUE_CAPACITY ON ... cross-joins four previous tables with tbl_venu-capacity, then left joins seven tables including tbl_VENUE_CAPACITY with tbl_VENUE_CAPACITY! FROM tbl_VENUE_AGE_XREF tvax, ... LEFT JOIN tbl_VENUE_AGE_XREF ON ... cross-joins five previous tables with tbl_VENUE_AGE_XREF, then left joins seven tables including tbl_VENUE_AGE_XREF with tbl_VENUE_AGE_XREF! FROM tbl_VENUE_AGES tvage ... LEFT JOIN ... tbl_VENUE_AGES ... cross-joins six previous tables with tbl_VENUE_AGES, then left joins seven tables including tbl_VENUE_AGES with tbl_VENUE_AGES! The double joins and cross joins will drive the server crazy. It's incoherent---the query makes no use of the double/cross/self-joins. Strong suggestion: lose the comma joins entirely, lose the duplicate joins, and write the join logic as explicit joins, for example SELECT ... FROM tourdates td INNER JOIN artist_tourdate AS artd USING (td_id) INNER JOIN tbl_artst AS art ON artd.artist_id = art.pkey INNER JOIN tbl_venues AS tv ON td.venue_id=tv.ID LEFT JOIN tbl_venue_capacity AS tvc ON tv.ID=tvc.venue_id LEFT JOIN tbl_venue_age_xref AS tvax ON tv.ID=tvax.Venue_id LEFT JOIN tbl_venue_ages AS tvage ON tvax.VENUE_ID = tvage.pkey WHERE td_date NOW() LIMIT 500 PB - Paul Nowosielski wrote: Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID , tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITEas ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEYAND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+-++---+--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +++---+-+-++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | td.venue_id| 1 | Using where | | tvage | ALL
Re: Left Join Help
Paul Nowosielski wrote: Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage These 5 tables are not joined on anything. LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+-++---+--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +++---+-+-++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | NULL | 4 | Using index; Distinct| +++---+-+-++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system load? Thank you, -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left Join Help
Here is your query rephrased a bit. I find this query structure easier to debug, especially when their are lots of joins. This is also the preferred structure in mysql 5 as I recall. Notice the ON ? part of the join. You didn't specify anything join condition so your doing a full join, very very bad. Fill in the question marks and your query should run fairly quick. SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td JOIN tbl_ARTST as art ON ? JOIN artist_tourdate artd ON ? JOIN tbl_VENUES tv ON ? JOIN tbl_VENUE_CAPACITY tvc ON ? JOIN tbl_VENUE_AGE_XREF tvax ON ? JOIN tbl_VENUE_AGES tvage ON ? LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 - Original Message - From: Paul Nowosielski [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, June 23, 2006 3:27 PM Subject: Left Join Help Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+-++---+--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +++---+-+-++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | NULL | 4 | Using index; Distinct| +++---+-+-++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system load? Thank you, -- Paul Nowosielski Webmaster -- 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: Left Join Help
I ammend my previous post. Paul Nowosielski wrote: Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage Use INNER_JOIN and use ON clauses. tvc, tvax, and tvage are not joined at all, producing Cartesian Products. LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) This on condition does not include the table being joined. LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) This on condition does not include the table being joined. LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) This on condition does not include the table being joined. WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+-++---+--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +++---+-+-++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | NULL | 4 | Using index; Distinct| +++---+-+-++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system load? Thank you, -- Gerald L. Clark Supplier Systems Corporation -- 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: Left Join Help
Paul, SELECT ... FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc , tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 FROM ... tbl_VENUE_CAPACITY tvc, ... LEFT JOIN tbl_VENUE_CAPACITY ON ... cross-joins four previous tables with tbl_venu-capacity, then left joins seven tables including tbl_VENUE_CAPACITY with tbl_VENUE_CAPACITY! FROM tbl_VENUE_AGE_XREF tvax, ... LEFT JOIN tbl_VENUE_AGE_XREF ON ... cross-joins five previous tables with tbl_VENUE_AGE_XREF, then left joins seven tables including tbl_VENUE_AGE_XREF with tbl_VENUE_AGE_XREF! FROM tbl_VENUE_AGES tvage ... LEFT JOIN ... tbl_VENUE_AGES ... cross-joins six previous tables with tbl_VENUE_AGES, then left joins seven tables including tbl_VENUE_AGES with tbl_VENUE_AGES! The double joins and cross joins will drive the server crazy. It's incoherent---the query makes no use of the double/cross/self-joins. Strong suggestion: lose the comma joins entirely, lose the duplicate joins, and write the join logic as explicit joins, for example SELECT ... FROM tourdates td INNER JOIN artist_tourdate AS artd USING (td_id) INNER JOIN tbl_artst AS art ON artd.artist_id = art.pkey INNER JOIN tbl_venues AS tv ON td.venue_id=tv.ID LEFT JOIN tbl_venue_capacity AS tvc ON tv.ID=tvc.venue_id LEFT JOIN tbl_venue_age_xref AS tvax ON tv.ID=tvax.Venue_id LEFT JOIN tbl_venue_ages AS tvage ON tvax.VENUE_ID = tvage.pkey WHERE td_date NOW() LIMIT 500 PB - Paul Nowosielski wrote: Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+-++---+--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +++---+-+-++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | NULL | 4 | Using index; Distinct| +++---+-+-++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system
Re: Left Join Help SOLVED
Thank you all so much for your help, here is my solution: (I'm sure I can do a little more optimization) SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td JOIN tbl_ARTST as art ON (art.PKEY = artd.artist_id) JOIN artist_tourdate artd ON (artd.artist_id = art.PKEY) JOIN tbl_VENUES tv ON (td.venue_id = tv.ID) LEFT JOIN tbl_VENUE_CAPACITY tvc ON (tvc.VENUE_ID = tv.ID) LEFT JOIN tbl_VENUE_AGE_XREF tvax ON (tvax.VENUE_ID = tv.ID) LEFT JOIN tbl_VENUE_AGES tvage ON (tvage.PKEY = tvax.VENUE_ID) LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) 45929 rows in set (3 min 11.75 sec) Best Regards, -- Paul Nowosielski Webmaster On Friday 23 June 2006 14:10, Gerald L. Clark wrote: I ammend my previous post. Paul Nowosielski wrote: Dear All, I've been hashing out this query for awhile with no luck as of yet. Basically the query works if I put a limit of 500 or so but when I do the full query it takes up so many resource that the database engine is useless. Here is the query: SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date, art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID, tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.ADDR2 as ven_add1,tv.CITY ven_city,tv.STATE ven_state, tv.ZIPCODE ven_zip,tv.COUNTRY ,tv.WEBSITE as ven_url,tvc.SIZE as capacity, tvage.TYPE as age,tv.TICKETAGNCY1 as tix0, tv.TICKETAGNCY2 as tix1 FROM tourdates td, tbl_ARTST as art, artist_tourdate artd , tbl_VENUES tv, tbl_VENUE_CAPACITY tvc ,tbl_VENUE_AGE_XREF tvax, tbl_VENUE_AGES tvage Use INNER_JOIN and use ON clauses. tvc, tvax, and tvage are not joined at all, producing Cartesian Products. LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID) This on condition does not include the table being joined. LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID) This on condition does not include the table being joined. LEFT JOIN tbl_VENUE_AGES ON (tvax.VENUE_ID = tvage.PKEY) This on condition does not include the table being joined. WHERE td_date NOW() AND (td.td_id = artd.td_id AND artd.artist_id = art.PKEY AND td.venue_id=tv.ID) LIMIT 500 Here is a description of the query: +++---+-+ -++---+--+ | table | type | possible_keys | key | key_len | | ref| rows | Extra| +++---+-+ -++---+--+ | td | range | PRIMARY,idx01,dateidx | dateidx | 4 | | NULL | 43943 | Using where; Using temporary | | artd | ref| idx01,idx02 | idx01 | 4 | | td.td_id | 1 | | | art| eq_ref | PRIMARY,idx02 | PRIMARY | 4 | | artd.artist_id | 1 | Using where | | tv | eq_ref | PRIMARY,idx04 | PRIMARY | 4 | | td.venue_id| 1 | Using where | | tvage | ALL| NULL | NULL|NULL | | NULL | 4 | | | tvc| ALL| NULL | NULL|NULL | | NULL | 10261 | | | tbl_VENUE_CAPACITY | index | NULL | idx01 | 5 | | NULL | 10261 | Using index; Distinct| | tvax | index | NULL | idx01 | 8 | | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGE_XREF | index | NULL | idx01 | 8 | | NULL | 11616 | Using index; Distinct| | tbl_VENUE_AGES | index | NULL | PRIMARY | 4 | | NULL | 4 | Using index; Distinct| +++---+-+ -++---+ I need to be able to run the full query on a daily basis without killing the DB engine. The query needs to pull in about 50,000 results. Does anyone see a way to optimize this query or rewrite it so it doesn't cause a huge system
Re: LEFT JOIN help (or come and slap the newbie)
Dan Hansen wrote: is essentially giving me what I need: CREATE TEMPORARY TABLE temptable SELECT state.name AS state , group.name AS group, group.zip AS zip, city.name AS city FROM city, group, zip LEFT JOIN state ON city.state_id = state.id WHERE group.zip = zip.zip AND zip.city_id = city.id; INSERT INTO temptable SELECT name, NULL, -1, NULL FROM state; SELECT state, group, zip, city FROM temptable ORDER BY state, zip, group ; (Once plugged into PHP code, I will replace temptable with a unique code-generated string). Should run in a single query too... SELECT state.name AS state , group.name AS group, group.zip AS zip, city.name AS city FROM city, group, zip LEFT JOIN state ON city.state_id = state.id WHERE group.zip = zip.zip AND zip.city_id = city.id UNION SELECT name, NULL, -1, NULL FROM state ORDER BY state, zip, group ; .. although untested. -do remove the semicolon twice- Hans -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN help (or come and slap the newbie)
You're confusing the left/right sides of LEFT JOIN... Using LEFT JOIN, it is the right table that is dependent on the left table. All results from left table are selected. So you may try: SELECT state.name AS state , group.name AS group, group.zip AS zip, city.name as city FROM state LEFT JOIN city ON city.state_id = state.id LEFT JOIN zip ON zip.city_id = city.id LEFT JOIN group ON group.zip = zip.zip Hope this helps, -- Diana Soares On Tue, 2003-10-14 at 22:27, D. R. Hansen wrote: Uberdumb question - but I'm still enough of a newbie that this is giving me fits... I have four tables, with relevant columns as follows: ++ ++ group zip -- -- name varchar city_id int zip mediumint zip mediumint ++ ++ state city -- -- id int id int name varchar name varchar state_id int [group]-n..1-[zip]-n..1-[city]-n..1-[state] I want my query to return a list that includes all states -- regardless of whether it matches any records in the other tables. The queries below (and I have tried many other permutations without success) returns only rows for states where there is a corresponding group record: SELECT state.name AS state , group.name AS group, group.zip AS zip, city.name as city FROM city, group, zip LEFT JOIN state ON city.state_id = state.id WHERE group.zip = zip.zip AND zip.city_id = city.id SELECT state.name AS state , group.name AS group, group.zip AS zip, city.name as city FROM group LEFT JOIN zip ON zip.zip = group.zip LEFT JOIN city ON city.id = zip.city_id LEFT JOIN state ON state.id = city.state_id My test data returns the following data (yes, all other tables are fully populated). +--+-+---+--+ | state| group | zip | city | +--+-+---+--+ | Illinois | Test Group | 60070 | Prospect Heights | +--+-+---+--+ 1 row in set (0.41 sec) I'd like to see +--+-+---+--+ | state| group | zip | city | +--+-+---+--+ . . . | Idaho| NULL| NULL | NULL | | Illinois | Test Group | 60070 | Prospect Heights | | Indiana | NULL| NULL | NULL | . . . etc... Can anyone tell me where I'm blowing it? Dan Hansen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN help (or come and slap the newbie)
I believe I tried that -- but when I did (and I just repeated it with the same result) mysql effectively hangs (i.e. the query takes interminably long -- I let it run for 20 minutes before killing it). So should I be looking at an indexing issue? Right now the only things indexed in the tables are their PKs -- their IDs. city has about 30K records; zip has about 40K. Thanks!! Dan At 03:51 AM 10/15/03, Diana Soares wrote: You're confusing the left/right sides of LEFT JOIN... Using LEFT JOIN, it is the right table that is dependent on the left table. All results from left table are selected. So you may try: SELECT state.name AS state , group.name AS group, group.zip AS zip, city.name as city FROM state LEFT JOIN city ON city.state_id = state.id LEFT JOIN zip ON zip.city_id = city.id LEFT JOIN group ON group.zip = zip.zip Hope this helps, -- Diana Soares On Tue, 2003-10-14 at 22:27, D. R. Hansen wrote: Uberdumb question - but I'm still enough of a newbie that this is giving me fits... I have four tables, with relevant columns as follows: ++ ++ group zip -- -- name varchar city_id int zip mediumint zip mediumint ++ ++ state city -- -- id int id int name varchar name varchar state_id int [group]-n..1-[zip]-n..1-[city]-n..1-[state] I want my query to return a list that includes all states -- regardless of whether it matches any records in the other tables. The queries below (and I have tried many other permutations without success) returns only rows for states where there is a corresponding group record: SELECT state.name AS state , group.name AS group, group.zip AS zip, city.name as city FROM city, group, zip LEFT JOIN state ON city.state_id = state.id WHERE group.zip = zip.zip AND zip.city_id = city.id SELECT state.name AS state , group.name AS group, group.zip AS zip, city.name as city FROM group LEFT JOIN zip ON zip.zip = group.zip LEFT JOIN city ON city.id = zip.city_id LEFT JOIN state ON state.id = city.state_id My test data returns the following data (yes, all other tables are fully populated). +--+-+---+--+ | state| group | zip | city | +--+-+---+--+ | Illinois | Test Group | 60070 | Prospect Heights | +--+-+---+--+ 1 row in set (0.41 sec) I'd like to see +--+-+---+--+ | state| group | zip | city | +--+-+---+--+ . . . | Idaho| NULL| NULL | NULL | | Illinois | Test Group | 60070 | Prospect Heights | | Indiana | NULL| NULL | NULL | . . . etc... Can anyone tell me where I'm blowing it? Dan Hansen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN help (or come and slap the newbie)
* D. R. Hansen At 03:51 AM 10/15/03, Diana Soares wrote: You're confusing the left/right sides of LEFT JOIN... Using LEFT JOIN, it is the right table that is dependent on the left table. All results from left table are selected. So you may try: [...] I believe I tried that -- but when I did (and I just repeated it with the same result) mysql effectively hangs (i.e. the query takes interminably long -- I let it run for 20 minutes before killing it). So should I be looking at an indexing issue? Right now the only things indexed in the tables are their PKs -- their IDs. city has about 30K records; zip has about 40K. Yes, you need to index you FK's. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN help (or come and slap the newbie)
For everyone who helped, THANK YOU!! For anyone who might be interested, here's what finally did the trick and is essentially giving me what I need: CREATE TEMPORARY TABLE temptable SELECT state.name AS state , group.name AS group, group.zip AS zip, city.name AS city FROM city, group, zip LEFT JOIN state ON city.state_id = state.id WHERE group.zip = zip.zip AND zip.city_id = city.id; INSERT INTO temptable SELECT name, NULL, -1, NULL FROM state; SELECT state, group, zip, city FROM temptable ORDER BY state, zip, group ; (Once plugged into PHP code, I will replace temptable with a unique code-generated string). Thanks again! Dan At 03:51 AM 10/15/03, Diana Soares wrote: You're confusing the left/right sides of LEFT JOIN... Using LEFT JOIN, it is the right table that is dependent on the left table. All results from left table are selected. So you may try: SELECT state.name AS state , group.name AS group, group.zip AS zip, city.name as city FROM state LEFT JOIN city ON city.state_id = state.id LEFT JOIN zip ON zip.city_id = city.id LEFT JOIN group ON group.zip = zip.zip Hope this helps, -- Diana Soares On Tue, 2003-10-14 at 22:27, D. R. Hansen wrote: Uberdumb question - but I'm still enough of a newbie that this is giving me fits... I have four tables, with relevant columns as follows: ++ ++ group zip -- -- name varchar city_id int zip mediumint zip mediumint ++ ++ state city -- -- id int id int name varchar name varchar state_id int [group]-n..1-[zip]-n..1-[city]-n..1-[state] I want my query to return a list that includes all states -- regardless of whether it matches any records in the other tables. The queries below (and I have tried many other permutations without success) returns only rows for states where there is a corresponding group record: SELECT state.name AS state , group.name AS group, group.zip AS zip, city.name as city FROM city, group, zip LEFT JOIN state ON city.state_id = state.id WHERE group.zip = zip.zip AND zip.city_id = city.id SELECT state.name AS state , group.name AS group, group.zip AS zip, city.name as city FROM group LEFT JOIN zip ON zip.zip = group.zip LEFT JOIN city ON city.id = zip.city_id LEFT JOIN state ON state.id = city.state_id My test data returns the following data (yes, all other tables are fully populated). +--+-+---+--+ | state| group | zip | city | +--+-+---+--+ | Illinois | Test Group | 60070 | Prospect Heights | +--+-+---+--+ 1 row in set (0.41 sec) I'd like to see +--+-+---+--+ | state| group | zip | city | +--+-+---+--+ . . . | Idaho| NULL| NULL | NULL | | Illinois | Test Group | 60070 | Prospect Heights | | Indiana | NULL| NULL | NULL | . . . etc... Can anyone tell me where I'm blowing it? Dan Hansen -- 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: left join help
Rick Pasotto wrote: One of these days I will maybe understand... A left join (t1 LEFT JOIN t2 ON condition) is defined as follows. For each row in t1, find all matching rows in t2 and return the combination of t1 and t2 found. If there are no t2s for a t1, leave the t2 values NULL in the result. Try: SELECT * FROM (history h LEFT JOIN members m ON h.member_id = m.id) LEFT JOIN activity a ON h.activity = a.id WHERE h.date = whatever.; Bruce Feist create table members ( id unsigned int autoincrement, name ) create table activity ( id unsigned int autoincrement, description ) create table history ( id unsigned in autoincrement, date date, member_id unsigned int, activity unsigned int ) What I need: 1) only records for a particular date 2) there should be at least one record for each activity 3) there may be multiples of the same activity on a given date 4) there may be multiples of the same member on a given date 5) not all members will be listed 6) the members.name result field may be NULL SELECT history.date, activity.description, members.name ??? WHERE history.date = '-MM-DD' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
