0

From my previous post I figured out that if I refer multiple columns in a select query I need a compound index, so for my table

CREATE TABLE price (
    dt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    marketId INT,
    buy DOUBLE,
    sell DOUBLE,
    PRIMARY KEY (dt, marketId),
    FOREIGN KEY fk_price_market(marketId) REFERENCES market(id) ON UPDATE CASCADE ON DELETE CASCADE
)  ENGINE=INNODB;

I created the compound index:

CREATE INDEX idx_price_market_buy ON price (marketId, buy, sell, dt);

now the query 

select max(dt) from price where marketId=309 and buy>0.3;

executes fast enough within 0.02 sec, but a similar query with the same combination of columns

select max(buy) from price where marketId=309 and dt>'2019-10-29 15:00:00';

takes 0.18 sec that is relatively slow.

descs of these queries look a bit different:

mysql> desc select max(dt) from price where marketId=309 and buy>0.3;
+----+-------------+-------+------------+-------+-----------------------------------------------------+----------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys                                       | key                  | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+-----------------------------------------------------+----------------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | price | NULL       | range | idx_price_market,idx_price_buy,idx_price_market_buy | idx_price_market_buy | 13      | NULL | 50442 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-----------------------------------------------------+----------------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select max(buy) from price where marketId=309 and dt>'2019-10-29 15:00:00';
+----+-------------+-------+------------+------+-----------------------------------------------+----------------------+---------+-------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys                                 | key                  | key_len | ref   | rows   | filtered | Extra                    |
+----+-------------+-------+------------+------+-----------------------------------------------+----------------------+---------+-------+--------+----------+--------------------------+
|  1 | SIMPLE      | price | NULL       | ref  | PRIMARY,idx_price_market,idx_price_market_buy | idx_price_market_buy | 4       | const | 202176 |    50.00 | Using where; Using index |
+----+-------------+-------+------------+------+-----------------------------------------------+----------------------+---------+-------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

for example, key_len differs. What does this mean?

And the main question: what is the difference between buy and dt columns? Why switching them places in the query affects the performance?

Alexey Starinsky
  • 3,699
  • 3
  • 21
  • 57
  • Possible duplicate of [Understanding MySQL key\_len in Explain Statement](https://stackoverflow.com/questions/7643491/understanding-mysql-key-len-in-explain-statement) – Raymond Nijland Nov 08 '19 at 13:34
  • .. keep in mind in some cased this `key_len` can be a estimate.. better is to use `EXPLAIN FORMAT=JSON query` instead gives better infromation about costs and read sizes.. – Raymond Nijland Nov 08 '19 at 13:34
  • @RaymondNijland the main question: what is the difference between `buy` and `dt` columns? Why switching them places in the query affects the performance? – Alexey Starinsky Nov 08 '19 at 13:37
  • *"the main question: what is the difference between buy and dt columns? Why switching them places in the query affects the performance?"* ...in short MySQL optimizer code base is a heavy beast to understand, In general MySQL 's optmizer is costs based and will use a acces plan which is most cheap ... Besides that that it is way to broad to answer here why and how it happens.. – Raymond Nijland Nov 08 '19 at 13:38
  • 3
    they are different because the index on `idx_price_market_buy ON price (marketId, buy, sell, dt)` can be used because the where clause has `marketId` and `buy` which match a prefix of the index, while for `marketId` and `dt` the index cannot be used for `dt`. It might help to create an index on `(marketId, dt)` or `dt, marketId` – Adder Nov 08 '19 at 13:43
  • 1
    But to add to @Adder 's comment, and [left side index prefixing usage](https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html) the query `select max(dt) from price where marketId=309 and buy>0.3;` needs atleast a index `price(marketId, buy)` or a covering `price(marketId, buy, dt)` the query `select max(buy) from price where marketId=309 and dt>'2019-10-29 15:00:00'` needs to have atleast a `price(marketId, dt)` or the covering `price(marketId, dt, buy)` – Raymond Nijland Nov 08 '19 at 13:46
  • ... besides that we can explain alot, but it might be better to read [Chapter 8 Optimization](https://dev.mysql.com/doc/refman/8.0/en/optimization.html) yourself in the MySQL manual.. – Raymond Nijland Nov 08 '19 at 13:49
  • @RaymondNijland I am not a reader, but writer. – Alexey Starinsky Nov 08 '19 at 13:50
  • *"I am not reader, but writer"* reading is something which you can't avoid to learn something new anyhow so your comment is a bit odd... – Raymond Nijland Nov 08 '19 at 14:02

0 Answers0