LEFT JOIN Optimization

[polycom]

Assistance needed to optimize this query

SELECT SD.content_id   AS Id, 
       SD.title        AS Title,
       CT.name         AS Contenttype,
       PV.content_id   AS SponsorId,
       PV.display_name AS Sponsor, 
 
CONCAT_WS("", NT.title, " - ", N.pubdate, " [", LG.id, "]") AS ListGroup,
 
CONCAT_WS("", N.format, " [", N.content_id, "]")            AS Newsletter,
 
L.description AS List,
LG.id         AS ListGroupId,
 
DATE_FORMAT(UC.clicktime, "%Y-%m-%d") AS ClickDate,
 
COUNT(UC.id) AS Clicks FROM reporting.user_clicks UC 
 
INNER JOIN sl__content.sponsordeals SD ON SD.content_id = UC.content_id 
 
AND SD.version_id =  (SELECT live_version_id FROM sl__content.content C_SD WHERE C_SD.id = SD.content_id)
 
INNER JOIN sl__content.content C ON C.id = SD.content_id 
 
INNER JOIN sl__content.content_types CT ON CT.id = C.content_type_id 
 
LEFT JOIN sl__content.newsletters N ON N.content_id = UC.nlid 
 
AND N.version_id =  (SELECT live_version_id FROM sl__content.content C_N WHERE C_N.id = N.content_id) 
 
LEFT JOIN sl__content.content CP ON CP.id = UC.placementid 
 
LEFT OUTER JOIN sl__content.content_relationships CR_NT ON CR_NT.content_id = N.content_id AND CR_NT.content_version_id = 
N.version_id AND CR_NT.relationship_id = 26 
 
LEFT OUTER JOIN sl__content.nl_types NT ON NT.content_id = CR_NT.related_content_id  AND NT.version_status = 'live'
 
LEFT OUTER JOIN sl__content.content_relationships CR_PV ON CR_PV.content_id = SD.content_id AND CR_PV.content_version_id = 
SD.version_id AND CR_PV.relationship_id = 11 
 
LEFT OUTER JOIN sl__content.providers PV ON PV.content_id = CR_PV.related_content_id  AND PV.version_status = 'live' 
 
LEFT JOIN qb.qbLists L ON L.id = N.listid 
 
LEFT JOIN qb.qbListGroups LG ON LG.id = L.listGroupId WHERE 1 AND UC.nlid = 0 AND 
 
UC.clicktime BETWEEN '20070901000000' AND '20071001000000' 
 
GROUP BY SD.content_id, ClickDate;
 
This is the explain plan i get... it takes abt 30 min for this query to run..Need help desperately...
 
 

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: SD
type: ALL
possible_keys: idx_content_id_version_id,idx_gordon_test
key: NULL
key_len: NULL
ref: NULL
rows: 13632
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: UC
type: ref
possible_keys: idx_nlid,idx_clicktime,idx_contentid
key: idx_contentid
key_len: 4
ref: sl__content.SD.content_id
rows: 1140
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: C
type: eq_ref
possible_keys: PRIMARY,content_type_id_idx
key: PRIMARY
key_len: 4
ref: sl__content.SD.content_id
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: CT
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: sl__content.C.content_type_id
rows: 1
Extra:
*************************** 5. row ***************************
id: 1
select_type: PRIMARY
table: N
type: ref
possible_keys: idx_content_id_version_id
key: idx_content_id_version_id
key_len: 4
ref: reporting.UC.nlid
rows: 1
Extra:
*************************** 6. row ***************************
id: 1
select_type: PRIMARY
table: CP
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: reporting.UC.placementid
rows: 1
Extra: Using index
*************************** 7. row ***************************
id: 1
select_type: PRIMARY
table: CR_NT
type: ref
possible_keys: relationship,content_id_version_idx
key: relationship
key_len: 12
ref: sl__content.N.content_id,sl__content.N.version_id, const
rows: 2
Extra: Using index
*************************** 8. row ***************************
id: 1
select_type: PRIMARY
table: NT
type: ref
possible_keys: idx_content_id_version_id,idx_status_content_id
key: idx_status_content_id
key_len: 5
ref: const,sl__content.CR_NT.related_content_id
rows: 1
Extra:
*************************** 9. row ***************************
id: 1
select_type: PRIMARY
table: CR_PV
type: ref
possible_keys: relationship,content_id_version_idx
key: relationship
key_len: 12
ref: sl__content.SD.content_id,sl__content.SD.version_i d,const
rows: 2
Extra: Using index
*************************** 10. row ***************************
id: 1
select_type: PRIMARY
table: PV
type: ref
possible_keys: idx_content_id_version_id,idx_status_content_id
key: idx_content_id_version_id
key_len: 4
ref: sl__content.CR_PV.related_content_id
rows: 1
Extra:
*************************** 11. row ***************************
id: 1
select_type: PRIMARY
table: L
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: sl__content.N.listid
rows: 1
Extra:
*************************** 12. row ***************************
id: 1
select_type: PRIMARY
table: LG
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: qb.L.listGroupID
rows: 1
Extra: Using index
*************************** 13. row ***************************
id: 3
select_type: DEPENDENT SUBQUERY
table: C_N
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: sl__content.N.content_id
rows: 1
Extra:
*************************** 14. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: C_SD
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: sl__content.SD.content_id
rows: 1
Extra:
14 rows in set (0.00 sec)

[pbmods]

Heya, polycom. Welcome to TSDN!

Please use CODE tags when posting source code:

[CODE=mysql]
MySQL code goes here.
[/CODE]

Whew, that query is a mess!

I'd rewrite it from scratch. There's subqueries where there should be joins, there are joins where they should probably just be taken out and made into separate queries and nobody took out the original "WHERE 1".

[polycom]

Heya, polycom. Welcome to TSDN!

Please use CODE tags when posting source code:

MySQL code goes here.
 

Whew, that query is a mess!

I'd rewrite it from scratch. There's subqueries where there should be joins, there are joins where they should probably just be taken out and made into separate queries and nobody took out the original "WHERE 1".

Hey pbmods,

I appreciate you looking into this and you are right .i want to break the query into two parts and probably create a temporary table for one of them...I am a newbie and not well versed with query tunning. Can you give me a query struture which i can follow for this query. or any example....

Also can you let me know what problems did you find in the query structure or any donts that any Mysql DBA should keep in mind while writing a query or tuning.

I hope i am not asking a lot from you, however i would greatly appreciate your guidance.

regards
POLYCOM

[pbmods]

Heya, POLYCOM.

First thing (minor), if you don't want a separator, use CONCAT(), not CONCAT_WS().

The presence of LEFT OUTER JOIN suggests that you really just need to execute multiple queries.

It looks like you're searching for several types of unrelated data and trying to mash them together into one result set.

If you don't have the ability to simplify this query, I strongly recommend that you bring in a professional.

[polycom]

Heya, POLYCOM.

First thing (minor), if you don't want a separator, use CONCAT(), not CONCAT_WS().

The presence of LEFT OUTER JOIN suggests that you really just need to execute multiple queries.

It looks like you're searching for several types of unrelated data and trying to mash them together into one result set.

If you don't have the ability to simplify this query, I strongly recommend that you bring in a professional.

thanks for your input.

The user_clicks table is a merge table and has got around 40 merge tables, however the condition is to search only two tables(as you can see the range of dates) i want to know that is there a possibility of creating a temporary table from this query.