MySQL select query is terribly slow

Question

I have a table with 2 196 998 records:

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;

The query

select max(buy) from price;

takes 1.92 sec that is a reasonable time and it takes 0.00 sec if I create an index on 'buy' column:

CREATE INDEX idx_price_buy ON price (buy);

And the query

select count(*) from price where marketId=309;

takes 0.05 sec and returns 160 570.

But the query

select max(buy) from price where marketId=309;

takes 15.49 sec (that is terribly huge) even if I create both idices:

CREATE INDEX idx_price_market ON price (marketId);
CREATE INDEX idx_price_buy ON price (buy);

(I am not sure, but probably index idx_price_market already exists because marketId column is needed in a foreign key constraint)

1) Is there a way to optimize it?

2) If no, what about other databases? Do they perform better?

EDIT1:

After creating the compound index

CREATE INDEX idx_price_market_buy ON price (marketId, buy);

the query takes 0.00 sec.

desc select max(buy) from price where marketId=309;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.01 sec)

you can test, if mysql uses the index with `desc`. So use `desc select max(buy) from price where marketId=309;` If mysql uses the index, it's written in the 'Extra' Column of that result — SOehl, Nov 08 '19 at 12:55
MySQL can only use one idea at a time. If you make reference to more than one column, then you may want to consider a compound index instead. — Strawberry, Nov 08 '19 at 13:10
Looks perfect: [Link](https://exceptionshub.com/meaning-of-select-tables-optimized-away-in-mysql-explain-plan.html) — SOehl, Nov 08 '19 at 13:14
Thats the relevant Info from the Link above: "The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizer determined that only one row should be returned." — SOehl, Nov 08 '19 at 13:17
Here's a lesson on building optimal indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql — Rick James, Dec 24 '19 at 22:15

score 7 · Accepted Answer · answered Nov 08 '19 at 12:51

7

select max(buy) from price where marketId=309;

Creating individual indexes on each column probably does not allow MySQL to optimize the query.

For this query, you want a compound index on (marketId, buy).

create index idx_price_market_buy ON price (marketId, buy);

The ordering of columns in the index matters: first the query filters on marketId (so you want this column in first position in the coumpound index), then it computes the maximum buy.

answered Nov 08 '19 at 12:51

GMB

216,147
25
84
135

2

*"Creating individual indexes on each column probably does not allow MySQL to optimize the query."* Indeed the tactic *"index shotgun"* ([term from a stackoverflow user Bill Karwin](https://www.slideshare.net/billkarwin/how-to-design-indexes-really)) will really *"back fire"* at some point ... – Raymond Nijland Nov 08 '19 at 12:54

MySQL select query is terribly slow

1 Answers1

Linked