0
SELECT coinlist_id, price, volume24, marketcap 
from coinprice_oneminute 
WHERE date_format(created,'%Y-%m-%d %H:%i') = date_format(date_sub('2019-02-11 10:20:02',interval 1 day) ,'%Y-%m-%d %H:%i')    
order by coinlist_id;

This query was working fine with same data set two days earlier but now it take very long time even for limit 1 query. The table from which I'm retrieving data is readable and writable. Any reason why this is happening?

New contributor
Saurabh is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

put on hold as too broad by Marco, mustaccio, Paul White Feb 11 at 23:02

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • You use functions. It causes fullscan and forbid indices usage. Use created field directly, without convertion to a string: WHERE `created` = '2019-02-11 10:20:02' - interval 1 day – Akina Feb 11 at 13:42
  • Is it truly the same query? Same environment too? – Jacob H Feb 11 at 13:47
  • Possible duplicate of Why is my query suddenly slower than it was yesterday? – mustaccio Feb 11 at 13:48
  • 1
    i need to match up to minutes only Create proper BETWEEN (or 2 unequiations). – Akina Feb 11 at 19:28
  • 1
    WHERE created >= '...' - INTERVAL 1 DAY - INTERVAL 1 MINUTE AND created < '...' - INTERVAL 1 DAY + INTERVAL 1 MINUTE . The principle is not to "hide" the column (created) inside a function (DATE_FORMAT). Also you need INDEX(created). As for "why" slower -- the table got bigger, and the table scan lead to blowing out cache. – Rick James Feb 12 at 3:57