can this query be optimized?
It can be written without joins?
What the fix is it?
I should have only one column in GROUP BY?
How to rewrite the query if that table is unrelated?
SSMs is sql server management studio
These are not DB related questions.
Ask in relevant groups
Above
I should have only one column in GROUP BY??
No, you can have as many as it is needed
I am not able to understand..that’s query gives the correct result what is required
NO, this is a happy coincidence only.
You have LIMIT 10 there in the query , how can you judge when you don’t even see all results?
This is because you use non-ANSI joins, don’t use them ever!
This is how the query should be like
SELECT
t1.department_name AS Department,
t3.display_name AS Assignee,
t4.s13 AS State,
COUNT(distinct(t4.id)) AS No_of_incidents
FROM `t_incidents` t4
JOIN `t_user_departments` t1 on t1.archive=FALSE
JOIN `ta_user_department` t2 on t2.department_id=t1.id
JOIN `t_core_users` t3 on t2.user_id= t3.id and t4.s17 = CONCAT(t3.display_name,’-‘,t3.id) AND t3.archive=FALSE
WHERE t4.archive=FALSE
AND t4.s17 IS NOT NULL
AND t4.s17 <>”
AND t4.is_template=FALSE
AND t4.created_on BETWEEN ‘156777777’ AND ‘1582195104000’
GROUP BY t4.s13,t4.s17 DESC
LIMIT 10;
Problems:
0) you don’t have any join condition on table t_user_departments t1
1) you have very bad join condition on t_core_users t3 — condition by expression, index will never be used,
2) you have wrong GROUP BY.
3) and you cover all this with LIMIT , just for you not to notice any problems in data.
This is wonderful result, I can say!
3 crucial problems in one query , and you ask for it OPTIMIZATION then!
This is because you use non-ANSI joins, don’t use them ever!
This is how the query should be like
SELECT
t1.department_name AS Department,
t3.display_name AS Assignee,
t4.s13 AS State,
COUNT(distinct(t4.id)) AS No_of_incidents
FROM `t_incidents` t4
JOIN `t_user_departments` t1 on t1.archive=FALSE
JOIN `ta_user_department` t2 on t2.department_id=t1.id
JOIN `t_core_users` t3 on t2.user_id= t3.id and t4.s17 = CONCAT(t3.display_name,’-‘,t3.id) AND t3.archive=FALSE
WHERE t4.archive=FALSE
AND t4.s17 IS NOT NULL
AND t4.s17 <>”
AND t4.is_template=FALSE
AND t4.created_on BETWEEN ‘156777777’ AND ‘1582195104000’
GROUP BY t4.s13,t4.s17 DESC
LIMIT 10;
Problems:
0) you don’t have any join condition on table t_user_departments t1
1) you have very bad join condition on t_core_users t3 — condition by expression, index will never be used,
2) you have wrong GROUP BY.
3) and you cover all this with LIMIT , just for you not to notice any problems in data.
This is wonderful result, I can say!
3 crucial problems in one query , and you ask for it OPTIMIZATION then!
Sorry for my misunderstanding. Thank you so much for your help..I will give this a try and come back with results
This query above is still WRONG !
create database “NewDatabse”
?
Then my guess was wrong…
SELECT t1.department_name AS Department,
t3.display_name AS Assignee,
t4.s13 AS State,
Count(t4.id) AS No_of_incidents
FROM t_user_departments t1,
t_incidents t4,
ta_user_department t2,
t_core_users t3
WHERE t2.department_id = t1.id
AND t2.user_id = t3.id
AND t1.archive = false
AND t4.archive = false
AND t4.s17 = Concat(t3.display_name, ‘-‘, t3.id)
AND t4.s17 IS NOT NULL
AND t4.s17 <> ”
AND t4.is_template = false
AND t3.archive = false
AND t4.created_on BETWEEN ‘156777777’ AND ‘1582195104000’
GROUP BY t4.s13,
t4.s17 DESC
LIMIT 10;
t3.display_name AS Assignee,
t4.s13 AS State,
Count(t4.id) AS No_of_incidents
FROM t_user_departments t1,
t_incidents t4,
ta_user_department t2,
t_core_users t3
WHERE t2.department_id = t1.id
AND t2.user_id = t3.id
AND t1.archive = false
AND t4.archive = false
AND t4.s17 = Concat(t3.display_name, ‘-‘, t3.id)
AND t4.s17 IS NOT NULL
AND t4.s17 <> ”
AND t4.is_template = false
AND t3.archive = false
AND t4.created_on BETWEEN ‘156777777’ AND ‘1582195104000’
GROUP BY t4.s13,
t4.s17 DESC
LIMIT 10;
Here i removed joins
you’d better leave them
t3.display_name AS Assignee,
t4.s13 AS State,
Count(t4.id) AS No_of_incidents
FROM t_user_departments t1,
t_incidents t4,
ta_user_department t2,
t_core_users t3
WHERE t2.department_id = t1.id
AND t2.user_id = t3.id
AND t1.archive = false
AND t4.archive = false
AND t4.s17 = Concat(t3.display_name, ‘-‘, t3.id)
AND t4.s17 IS NOT NULL
AND t4.s17 <> ”
AND t4.is_template = false
AND t3.archive = false
AND t4.created_on BETWEEN ‘156777777’ AND ‘1582195104000’
GROUP BY t4.s13,
t4.s17 DESC
LIMIT 10;
this is WRONG select, either.
NO join condition for t1 and t4
NO join condition for t1 and t4
There is no direct relationship between t4 and t1.
This is very bad, then these tables shouldn’t be together in a select
Hey llia I appreciate for your patience..Can you guide me to a tutorial to learn about this.
There’s no join condition for table t1 at all, do you understand?
t3.display_name AS Assignee,
t4.s13 AS State,
Count(t4.id) AS No_of_incidents
FROM t_user_departments t1,
t_incidents t4,
ta_user_department t2,
t_core_users t3
WHERE t2.department_id = t1.id
AND t2.user_id = t3.id
AND t1.archive = false
AND t4.archive = false
AND t4.s17 = Concat(t3.display_name, ‘-‘, t3.id)
AND t4.s17 IS NOT NULL
AND t4.s17 <> ”
AND t4.is_template = false
AND t3.archive = false
AND t4.created_on BETWEEN ‘156777777’ AND ‘1582195104000’
GROUP BY t4.s13,
t4.s17 DESC
LIMIT 10;
You have 4 tables, then you should have at least 3 join conditions between them, you have got only 2
This is just skill of writing SQL, there is no tutorials on this
How to rewrite the query if that table is unrelated?
I don’t know, I don’t know tables structure
I so it, but the structure is not understandable.
I can read it , I can’t understand it
So what you should do now is studying the DB and writion the query from scratch again.
There are more errors there.