Mysql Question posted in
The official MySQL documentation can be found here.

In Mysql Query : After adding DATE() in Groupby taking more than 15 sec, otherwise 1sec

Here is the query, here al.activated_date column is having timestamp. I want to group by application.id, application_logs.activated date.

When i remove DATE(al.activated_date), query excution time is 1.2sec.

But when i use it, exceeds 15sec..

SELECT 
    a.id, a.description, DATE(al.activated_date) as activated_date, a.category, COUNT(a.id) as total
FROM 
    application_logs al
JOIN 
    applications a ON al.behavior_id = a.id
JOIN 
    users u ON al.manager_id = u.id 
WHERE
  u.year_id = 3
AND 
  u.shift_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,16)
and u.is_active = 1
AND 
  a.system_id IN ("43","70","68","69","19","20","45","44","77","46","47","78","11","53","62","63","7","3","50","65","64","73","66","4","12","82","75","26","76","1","2","13","51","42","67","85","14","5","52","8","48","17","71","58","60","79","80","81","18","6","21","22","55","83","23","84","24","25","56","9","27","28","31","29","32","33","34","10","35","36","15","37","38","39","59","40","41","72","16")
AND   
  a.id IN (4500+ ids)
AND 
  DATE(al.activated_date) >= "2024-07-31"
AND 
  DATE(al.activated_date) <= "2025-05-30"
AND 
  a.category in ('aa','bb')
GROUP BY 
  a.id, DATE(al.activated_date);

Please review my query, and kindly share some suggestion or ideas to achieve the same response.

Tags:

2

Answers


  1. 0

    When you call a function on an column, the column index can’t be used, so it slows down queries. Instead of filtering using the index, it has to scan all the rows, call the function, and then do the comparison.

    Instead of converting the timestamp to a date, just compare with the appropriate times of day.

    AND al.activated_date BETWEEN '2024-07-31 00:00:00' AND '2025-05-30 23:59:59'
    Login or Signup to reply.
  2. 0

    This seems only natural:

    • With the ID the DBMS just have to count all its rows.
    • With the DATE(al.activated_date) added the DBMS will have to take every row, invoke the DATEfunction on the timestamp, and then sort all the resulting dates to get the aggregation groups. That may take a while.

    If you want to speed this up, you should store the date as a computed value with the timestamp and then index it.

    ALTER TABLE application_logs
  ADD COLUMN onlydate DATE GENERATED ALWAYS AS (DATE(activated_date)) STORED;

CREATE INDEX idx ON application_logs (onlydate);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search