The following query takes a long time to execute because the table has lots ( Millions of records) of data so i would like to optimize the query so the execution will be faster and i will get results faster.
when i execute the following query then it hangs or takes long time to execute and quit.
Can any one help me to resolve the issue please?
SELECT expired_calls.* from
(SELECT distinct c.* FROM laes_calls c, laes_messages m, retention_policies r, recordable_entities e
WHERE ((c.id = m.laes_call_id) AND (c.retention_policy_id = r.id) AND (c.case_id = e.id)
AND ((TO_DAYS(CURRENT_DATE) - TO_DAYS(m.timestamp)) > r.call_retention_days)
AND (r.call_retention_days > 0)
AND ((r.eval_retention_rule = 'DELETE_CALLS_AND_EVALS') OR (r.eval_retention_rule = 'DELETE_CALLS_KEEP_EVALS')
OR ((r.eval_retention_rule = 'KEEP_CALLS_AND_EVALS')
AND (c.id not in (select c2.id from laes_calls c2, performance_evaluations p where c2.id = p.call_id) ) ) )
AND (e.legal_hold = 0)
AND (c.id >1) )
SELECT DISTINCT c.* FROM laes_calls c, laes_messages m
WHERE ((c.id >1) AND (c.save = 0) AND (m.laes_call_id = c.id) AND ((TO_DAYS(CURRENT_DATE) - TO_DAYS(m.timestamp)) > 1))
SELECT DISTINCT c.* FROM laes_calls c, laes_messages m, release_messages r
WHERE ((c.id >1) AND (c.save = 0) AND (m.laes_call_id = c.id) AND (m.id = r.id))
) AS expired_calls
ORDER BY expired_calls.id ASC;
first, you can try to run the queries separately instead of union and see which one is taking more time. Also, you can try to break the 1st query into simple queries and create temporary tables and use the tables to join with other tables.
I tried to seperate queries. First query runs too slow and timeouts when I have few millions of rows. other queries are fine. Can you suggest any changes in first query to make it faster? I already have indexes, still takes 5500 sec to run.
Without DDL and Explain it's hard to tell.
NOT IN in an ORed condition might be bad, you probably don't need the join and can rewrite it with a simple NOT EXISTS:
OR NOT EXISTS (select * from performance_evaluations p where c.id = p.call_id) ) )
What is TO_DAYS, a UDF? You don't need it to calculate the number of days between two dates, this is not MySQL.
Btw, this is not the actual query as TIMESTAMP is a keyword and will return a syntax error.
In most of the above joins the tables are related via the .id field. In the first query m is joined to r only on the inequality on the date expression which will force a full product join comparing every row of m to every row of r. If either or both of those are any size, it will take a while.
Thank you Dnoeth,
I made change as you suggested OR NOT EXISTS (select * from performance_evaluations p where c.id = p.call_id) ) ). which is little better. but still very slow.
Thanks ToddAWalter, you are correct. I need to go to every tow and calculate days.
(TO_DAYS(CURRENT_DATE) - TO_DAYS(m.timestamp)) > r.call_retention_days)
is there any way I can make it faster? I use this 2 times in one query.