query taking really long time.
I have an issue where I am not even after adding indexes and removing the date function the query is still performing slow.
ALTER TABLE ping_post.sell_ping_log ADD COLUMN computed_created Date AS (date (created)) PERSISTENT;
CREATE INDEX PP_SPL_CREATED ON ping_post.sell_ping_log(lead_id, sell_id, computed_created);
Below is the execution plan after the creation of column and index.
MariaDB [ping_post]> Explain Select p.id, s.id AS campaign_id, p.computed_created, p.response_status_id AS status_id, s.seller_id, l.name AS lead, p.zip, 1 AS tier_id, p.channel_id from ping_post.sell_ping_log p
-> INNER JOIN settings.lead l ON l.id = p.lead_id
-> INNER JOIN ping_post.sell s ON s.seller_id = 19 and s.id = p.sell_id
-> where p.computed_created >= (curdate() - interval 10 day) order by p.computed_created DESC;
+------+-------------+-------+--------+-------------------------------------------+-----------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+-------------------------------------------+-----------+---------+---------------------+------+----------------------------------------------+
| 1 | SIMPLE | s | ref | PRIMARY,seller_id | seller_id | 4 | const | 28 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | p | ref | sell_id,sell_ping_log_idx1,PP_SPL_CREATED | sell_id | 4 | ping_post.s.id | 3632 | Using where |
| 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 4 | ping_post.p.lead_id | 1 | |
+------+-------------+-------+--------+-------------------------------------------+-----------+---------+---------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)
Explain Select p.id, s.id AS campaign_id, p.created, p.response_status_id AS status_id, s.seller_id, l.name AS lead, p.zip, 1 AS tier_id, p.channel_id from ping_post.sell_ping_log p
INNER JOIN settings.lead l ON l.id = p.lead_id
INNER JOIN ping_post.sell s ON s.seller_id = 19 and s.id = p.sell_id where p.computed_created BETWEEN curdate() - interval 10 day and curdate() order by p.created DESC;
Therefore I tried in the below ways
Explain Select p.id, s.id AS campaign_id, p.created, p.response_status_id AS status_id, s.seller_id, l.name AS lead, p.zip, 1 AS tier_id, p.channel_id from ping_post.sell_ping_log p
INNER JOIN settings.lead l ON l.id = p.lead_id
INNER JOIN ping_post.sell s ON s.seller_id = 19 and s.id = p.sell_id
where p.computed_created >= (curdate() - interval 10 day)
And p.computed_created <= (curdate()) order by p.created DESC;
Explain Select p.id, s.id AS campaign_id, p.computed_created, p.response_status_id AS status_id, s.seller_id, l.name AS lead, p.zip, 1 AS tier_id, p.channel_id from ping_post.sell_ping_log p
INNER JOIN settings.lead l ON l.id = p.lead_id
INNER JOIN ping_post.sell s ON s.seller_id = 19 and s.id = p.sell_id
where p.computed_created >= (curdate() - interval 10 day) order by p.computed_created DESC;
Still the query is giving same results of 1.4x mins approximately.