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" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to