4

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.

  • All indexes beginning with corr_id will be quite ineffective when your only check is corr_id is not null and not any real equality. – jkavalik Dec 17 '15 at 7:07
1

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 > 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;

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

  • This one improved the searching a lot (So thanks to @a_vlad and @rick-james). I have also read and been advised by others, will there be an further significant and noticeable performance increase if I split the created timestamp DATETIME into 2 columns DATE and TIME. Because I usually query by date and not really by time. – Dynameyes Dec 17 '15 at 8:39
  • columns in index must be in same order as used in query - sorry but no, thats not true - they should be in the order they will be best used and that has nothing to do with the order in which the columns and conditions are written – jkavalik Dec 17 '15 at 9:01
  • just to clarify, I mean this - dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html – a_vlad Dec 17 '15 at 9:31
1

A 'range' makes the index useless after it. That is, INDEX(corr_id, x) won't get to the x because of corr_id IS NOT NULL.

I recommend INDEX(created_timestamp) for the query presented. It should handle part of the WHERE, the ORDER BY and the LIMIT.

If > 200 is rare enough, INDEX(http_status_code) might be useful. Put both of them on the table; let the optimizer pick.

What are the other queries? Maybe they need something else?

my Index Cookbook.

  • I would suggest (created_timestamp, http_status_code) and maybe even extending by corr_id if the available MySQL version can use Index Condition Pushdown – jkavalik Dec 17 '15 at 7:09
  • @jkavalik - Good idea. I would like to see a timing comparison for such. I would guess "up to twice as fast". – Rick James Dec 17 '15 at 19:28
  • depends on the rarity of the additional conditions, but I ran some simple test on our DB - pastebin.com/C2ckiJMB (seems like MariaDB marks range access as Using index condition; even when the used index does not actually contain any additional column to check). – jkavalik Dec 18 '15 at 6:23
  • I think it refers to "ICP", which implies that the engine is given the opportunity to filter out the row. Before ICP existed, the row was fed back to the handler after using the index; then the handler checked the rest of the WHERE clauses. MariaDB and Oracle may be different on how much the engine actually handles, especially since Oracle effectively looks only at InnoDB. ICP saves time by avoiding copying the row up the change. – Rick James Dec 18 '15 at 6:36
  • It does, I was just surprised it shows up even in the case when the used index does not contain any additional columns so ICP has no actual opportunity to help - but it probably does not hurt anyway. The timing results are really in favor of using ICP where possible (by extending the keys even after first range access - making them "condition-covering" instead of fully covering) – jkavalik Dec 18 '15 at 6:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.