Results 1 to 2 of 2

Thread: SQL Tuning

  1. #1
    Join Date
    May 2016
    Posts
    1

    Unanswered: SQL Tuning

    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.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    try reversing the order of the columns in your index, That should help improve the response time.

    CREATE INDEX PP_SPL_CREATED ON ping_post.sell_ping_log(computed_created, sell_id, lead_id );
    Dave

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •