I am trying to optimise the below sql query but am not finding any way to do it:
SELECT DISTINCT ( SELECT COUNT(followers.follower_id) FROM followers WHERE followers.follow_start_date <= dates.start_date ) AS running_total , dates.onboarded_date FROM followers RIGHT JOIN ( select distinct follow_start_date as start_date from followers where follow_start_date <= "2020-10-01" ) dates ON followers.follow_start_date = dates.start_date ORDER BY dates.start_date ;
the above query gives me a running total of followers on day basis. So I am first getting list of unique start dates from table and joining the query that counts followers up until given day.
it gives me output like below:
running_total,start_date 498,2020-08-14 00:00:00 499,2020-08-17 00:00:00 500,2020-09-04 00:00:00 503,2020-09-30 00:00:00
any help to optimise this above query or write the query in a better way to get the same result is appreciated.
Edit: explain query output:
1,PRIMARY,followers,,ALL,,,,,926,100,Using temporary; Using filesort 1,PRIMARY,<derived3>,,ref,<auto_key0>,<auto_key0>,6,follower_app.followers.follow_start_date,10,100,Using index 3,DERIVED,followers,,ALL,,,,,926,33.33,Using where; Using temporary 2,DEPENDENT SUBQUERY,followers,,ALL,,,,,926,3.33,Using where
A better way in mysql or sqlalchemy would be perfect