MySQL Forums
Forum List  »  Performance

Re: query taking really long time.
Posted by: Rick James
Date: May 23, 2016 02:58PM

Virtual columns is distracting you from the real problems. Ignore (or remove) `computed_created` and change to

    Select  p.id,
            s.id AS campaign_id,
            p.created,           -- note
            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.id = p.sell_id   -- note
        where  p.created >= (curdate() - interval 10 day)   -- note
          AND  s.seller_id = 19       -- note: moved from ON
        order by  p.created DESC;
-- and have these indexes:
--
sell:  INDEX(seller_id, s_id)   -- "covering index"
sell_ping_log:  INDEX(created)  -- this should suffice
lead:  INDEX(id) -- unless you already have PRIMARY KEY(id)

The optimizer may choose to start with `s` or with `p`. I provided indexes for each.

INDEX(lead_id, sell_id, computed_created) is rather useless. The filtering column(s) should be first, not last. You are filtering on `computed_created` (or simply `created`), so it should be first. Since you need a "range" over that column, the subsequent columns will not be used in the index. See http://mysql.rjweb.org/doc.php/index_cookbook_mysql

I am assuming that `created` is of datatype DATE. If not, I may need to alter some of what I have said.

Adding "AND created < cURDATE()" (or using BETWEEN) will not change the performance unless there are a lot of 'future' entries in the table.

Please provide SHOW CREATE TABLE for each table before discussing further.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: query taking really long time.
404
May 23, 2016 02:58PM


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.