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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
