Here is your query rephrased a bit. I find this query structure easier to debug,
especially when their are lots of joins. This is
also the preferred structure in mysql 5 as I recall.
Notice the ON ? part of the join. You didn't specify anything join condition so your doing
a full join, very very bad. Fill in the
question marks and your query should run fairly quick.
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 ?
JOIN artist_tourdate artd ON ?
JOIN tbl_VENUES tv ON ?
JOIN tbl_VENUE_CAPACITY tvc ON ?
JOIN tbl_VENUE_AGE_XREF tvax ON ?
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
----- Original Message -----
From: "Paul Nowosielski" <paul@stripped>
To: <mysql@stripped>
Sent: Friday, June 23, 2006 3:27 PM
Subject: Left Join Help
> 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,
>
> --
> Paul Nowosielski
> Webmaster
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
