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 > 200 AND created_timestamp >= '2015-11-01 23:00:00' AND created_timestamp <= '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.