mysql - Need help in improving performance of SQL query -

take @ sqlfiddle made http://sqlfiddle.com/#!9/4b903/2/0. simplified version of database. have million records in history , problem query seems slow. takes minute result. i'm not in sql stuff. guess there columns need index im not sure those.

update:

i tried explain sql

  id | select_type        | table | type  | possible_keys                                       | key               | key_len | ref          | rows  | 1  | primary            | t1    | range | created_reason,created_reason_2                     | created_reason_2  | 6       | null         | 91136 | using where; using temporary; using filesort 2  | dependent subquery | t2    | ref   | history_table1_id_foreign,table1_id,table1_id_2     | table1_id_2       | 4       | t1.table1_id | 11    | using where; using index; using filesort  

you haven't created indexes on of columns. please read linear , binary searches before write heavy queries.

add indexes table1

alter table `test_delete`.`table1`  add index `idx_created_at` (`created_at` asc), add index `idx_price` (`price` asc); 

add indexes history

alter table `test_delete`.`history`  add index `idx_history_created_at` (`created_at` asc), add index `idx_history_price` (`price` asc), add index `idx_table1_id` (`table1_id` asc); 

a minor change - won't have effect

select t1.* history t1 t1.created_at >= '2016-03-13 00:00:00' , t1.created_reason = 'scraped' , t1.price not in (-1, (          select t2.price            history t2           t2.table1_id = t1.table1_id             , t2.created_at  < t1.created_at             , t2.created_at  > t1.created_at + interval -30 day             , t2.price > -1           order t2.created_at desc           limit 1        )) group t1.table1_id;