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