MySQL Forums
Forum List  »  Performance

query taking really long time.
Posted by: Mehraz Ahmed Sheikh
Date: May 16, 2016 09:45AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
query taking really long time.
1534
May 16, 2016 09:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.