List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:June 23 2006 8:16pm
Subject:Re: Left Join Help
View as plain text  
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 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

Thread
Left Join HelpPaul Nowosielski23 Jun
  • Re: Left Join HelpGerald L. Clark23 Jun
  • Re: Left Join HelpBrent Baisley23 Jun
  • Re: Left Join HelpGerald L. Clark23 Jun
    • Re: Left Join Help SOLVEDPaul Nowosielski23 Jun
  • Re: Left Join HelpPeter Brawley23 Jun
    • Re: Left Join HelpDaniel McQuay24 Jun