List:General Discussion« Previous MessageNext Message »
From:Daniel McQuay Date:June 24 2006 2:41pm
Subject:Re: Left Join Help
View as plain text  
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 <peter.brawley@stripped> 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/mysql?unsub=1
>
>


-- 
Daniel McQuay
simplebob@stripped
boxster.homelinux.org
H: 814.825.0847
M: 814-341-6233

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