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 | 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, > > -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.3/374 - Release Date: 6/23/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Daniel McQuay [EMAIL PROTECTED] boxster.homelinux.org H: 814.825.0847 M: 814-341-6233