List:General Discussion« Previous MessageNext Message »
From:Paul Nowosielski Date:June 23 2006 8:34pm
Subject:Re: Left Join Help SOLVED
View as plain text  
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/mysql?unsub=1

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