473,216 Members | 1,329 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

LEFT JOIN Optimization

Assistance needed to optimize this query
Expand|Select|Wrap|Line Numbers
  1. SELECT SD.content_id   AS Id, 
  2.        SD.title        AS Title,
  3.        CT.name         AS Contenttype,
  4.        PV.content_id   AS SponsorId,
  5.        PV.display_name AS Sponsor, 
  6.  
  7. CONCAT_WS("", NT.title, " - ", N.pubdate, " [", LG.id, "]") AS ListGroup,
  8.  
  9. CONCAT_WS("", N.format, " [", N.content_id, "]")            AS Newsletter,
  10.  
  11. L.description AS List,
  12. LG.id         AS ListGroupId,
  13.  
  14. DATE_FORMAT(UC.clicktime, "%Y-%m-%d") AS ClickDate,
  15.  
  16. COUNT(UC.id) AS Clicks FROM reporting.user_clicks UC 
  17.  
  18. INNER JOIN sl__content.sponsordeals SD ON SD.content_id = UC.content_id 
  19.  
  20. AND SD.version_id =  (SELECT live_version_id FROM sl__content.content C_SD WHERE C_SD.id = SD.content_id)
  21.  
  22. INNER JOIN sl__content.content C ON C.id = SD.content_id 
  23.  
  24. INNER JOIN sl__content.content_types CT ON CT.id = C.content_type_id 
  25.  
  26. LEFT JOIN sl__content.newsletters N ON N.content_id = UC.nlid 
  27.  
  28. AND N.version_id =  (SELECT live_version_id FROM sl__content.content C_N WHERE C_N.id = N.content_id) 
  29.  
  30. LEFT JOIN sl__content.content CP ON CP.id = UC.placementid 
  31.  
  32. LEFT OUTER JOIN sl__content.content_relationships CR_NT ON CR_NT.content_id = N.content_id AND CR_NT.content_version_id = 
  33. N.version_id AND CR_NT.relationship_id = 26 
  34.  
  35. LEFT OUTER JOIN sl__content.nl_types NT ON NT.content_id = CR_NT.related_content_id  AND NT.version_status = 'live'
  36.  
  37. LEFT OUTER JOIN sl__content.content_relationships CR_PV ON CR_PV.content_id = SD.content_id AND CR_PV.content_version_id = 
  38. SD.version_id AND CR_PV.relationship_id = 11 
  39.  
  40. LEFT OUTER JOIN sl__content.providers PV ON PV.content_id = CR_PV.related_content_id  AND PV.version_status = 'live' 
  41.  
  42. LEFT JOIN qb.qbLists L ON L.id = N.listid 
  43.  
  44. LEFT JOIN qb.qbListGroups LG ON LG.id = L.listGroupId WHERE 1 AND UC.nlid = 0 AND 
  45.  
  46. UC.clicktime BETWEEN '20070901000000' AND '20071001000000' 
  47.  
  48. GROUP BY SD.content_id, ClickDate;
  49.  
  50. This is the explain plan i get... it takes abt 30 min for this query to run..Need help desperately...
  51.  
  52.  

*************************** 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)
Oct 12 '07 #1
4 2687
pbmods
5,821 Expert 4TB
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".
Oct 12 '07 #2
Heya, polycom. Welcome to TSDN!

Please use CODE tags when posting source code:

Expand|Select|Wrap|Line Numbers
  1. MySQL code goes here.
  2.  
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
Oct 15 '07 #3
pbmods
5,821 Expert 4TB
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.
Oct 17 '07 #4
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.
Oct 26 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Marek Lewczuk | last post by:
Hello, I have a strange problem, maybe some of you will be able to explain me something. I use LEFT JOIN as a substitute for subselects. It's true that many subselects can be rewriten using LEFT...
0
by: Petre Agenbag | last post by:
Hi List Me again. I'm trying to return from multiple tables, the records that have field "information_sent" between two dates. The tables are all related by means of the id of the entry in the...
0
by: Soefara | last post by:
Dear Sirs, I am experiencing strange results when trying to optimize a LEFT JOIN on 3 tables using MySQL. Given 3 tables A, B, C such as the following: create table A ( uniqueId int not...
1
by: Paul Bramscher | last post by:
Here's one for pathological SQL programmers. I've got a table of things called elements. They're components, sort of like amino acids, which come together to form complex web pages -- as nodes...
4
by: jbm05 | last post by:
Hi, I'm curious about the computational complexity of a query I have. The query contains multiple nested self left joins, starting with a simple select, then doing a self left join with the...
3
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but...
2
by: tricard | last post by:
Good day all, I have a large outer joined query that I want to have some criteria. The select query is gathering all part numbers from tblPartNumbers, left joining to tblPartNumberVendor (since...
9
by: shanevanle | last post by:
I have two tables that are pretty big. I need about 10 rows in the left table and the right table is filtered to 5 rows as well. It seems when I join the tables in the FROM clause, I have to...
1
by: naveenchhibber | last post by:
Hi all pls tell me that the following statment is valid in oracle 9i or 10g.. update ws set received_by_facility = coalesce(rbf_ouk.organizational_unit_id, 0), ...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.