Indexed but still slow during query I have a table that stores REST requests and responses:

CREATE TABLE ws_call_logs
(
   id                 BIGINT         NOT NULL,
   ws_method_id       BIGINT         NOT NULL,
   device_id          BIGINT,
   session_id         BIGINT,
   corr_id            BIGINT,
   request            MEDIUMBLOB,
   response           MEDIUMBLOB,
   http_status_code   INT UNSIGNED   NOT NULL,
   processing_status  CHAR(1)        NOT NULL,
   created_timestamp  DATETIME       NOT NULL
)


I created 4 indexes representing the 4 queries I usually execute:

CREATE INDEX ix_corr_id_timstamp ON ws_call_logs (corr_id, created_timestamp);
CREATE INDEX ix_corr_method_timstamp ON ws_call_logs(corr_id, ws_method_id, created_timestamp);
CREATE INDEX ix_corr_id_status_timstamp ON ws_call_logs (corr_id, http_status_code, created_timestamp);
CREATE INDEX ix_corr_method_status_timstamp ON ws_call_logs (corr_id, ws_method_id, http_status_code, created_timestamp);


Here is the query:

SELECT *
FROM
   ws_call_logs
WHERE
   (corr_id is not null
      AND http_status_code &gt; 200
      AND created_timestamp &gt;= '2015-11-01 23:00:00'  
      AND created_timestamp &lt;= '2015-11-30 23:00:00'
   ) 
ORDER BY
   created_timestamp
DESC LIMIT 25;


I am expecting that it should be fast because it should use the 3rd index that I have created. But it seems it is not and it takes around 1 minute before it returns.

Can anybody spot what is the problem? Or any tips how to debug?

And any other tips to optimise the queries will be appreciated.
first what You need, check explain plan of query:

EXPLAIN SELECT *
FROM
   ws_call_logs
WHERE
   (corr_id is not null
      AND http_status_code &gt; 200
      AND created_timestamp &gt;= '2015-11-01 23:00:00'  
      AND created_timestamp &lt;= '2015-11-30 23:00:00'
   ) 
ORDER BY
   created_timestamp
DESC LIMIT 25;


It return You information about which indexes from present MySQL will use when run query

Than You will check cardinality of the indexes

SHOW INDEX FROM ws_call_logs


it give You idea - which index better to use in this case

without information about data, general ideas:
 - index for created_timestamp - good candidate

You can or create index separate for created_timestamp only, or create index for 3 columns:
core_id, hit_status_code, created_timestamp - columns in index must be in same order as used in query

and last SELECT * not give ideas about data size, even You request 25 records, but before server must sort records by DESC