mysql: what is the best way to index multicolum in this scenario

user_2897115 0
Viewed 36

I have the following sql command which takes lot of time. I am thinking of indexing

SELECT `daily_price_history`.`high`,
       `daily_price_history`.`low`,
       `daily_price_history`.`open`,
       `daily_price_history`.`datetime`,
       `daily_price_history`.`close`,
       `daily_price_history`.`creation_time`
FROM `daily_price_history`
WHERE (`daily_price_history`.`datetime_utc` BETWEEN '2015-12-04 18:43:28.710229' AND '2020-12-02 18:43:28.710229'
       AND `daily_price_history`.`symbol` = 'A')
ORDER BY `daily_price_history`.`creation_time` ASC

So indexing multiple columns (datetime_utc,symbol) is the best way or index two separate columns.

My SHOW CREATE TABLE

CREATE TABLE `daily_price_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `symbol` varchar(50) NOT NULL,
  `symbolExpectionCount` int(11) NOT NULL,
  `url` longtext NOT NULL,
  `volume` double NOT NULL,
  `high` double NOT NULL,
  `low` double NOT NULL,
  `open` double NOT NULL,
  `datetime` bigint(20) NOT NULL,
  `datetime_utc` datetime(6) NOT NULL,
  `close` double NOT NULL,
  `creation_time` datetime(6) NOT NULL,
  `lot_time` datetime(6) NOT NULL,
  `periodType` varchar(50) NOT NULL,
  `symbolId_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `daily_price_history_symbolId_id_30077f62_fk_symbol_list_id` (`symbolId_id`),
  KEY `datetime` (`datetime`),
  CONSTRAINT `daily_price_history_symbolId_id_30077f62_fk_symbol_list_id` FOREIGN KEY (`symbolId_id`) REFERENCES `symbol_list` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11549349 DEFAULT CHARSET=utf8mb4

I have a table called symbol_list, so here symbol is just text of symbol, whereas symbolId_id is a foreign key relation to the symbol_list

indexing query-optimization mysql
2 Answers

MySQL will use just one index per table reference in most cases. So creating two separate indexes is not the best strategy.

You would be better off with an index on (symbol, datetime_utc). The order of columns in the index is important!

Your query has an equality condition for symbol so it should go first.

The range condition for datetime_utc makes it better to put that second.

You can't optimize for both a range condition and the ORDER BY. You have to pick one or the other to optimize.

So the index should be one of:

  • (symbol, datetime_utc) - optimizes for both search terms, but this will require a filesort of the result.

  • (symbol, creation_time) - optimizes for the ORDER BY, avoiding the filesort, but won't optimize for the datetime_utc condition.

Which one is better? It depends on your data, and how many rows match your conditions. There's no simple answer, you'll just have to test both.

You might like to view my presentation How to Design Indexes, Really, or the video of me presenting it.

Bill_Karwin_208600

You need a PRIMARY KEY. It needs to be unique. If you can guarantee that (symbol, datetime_utc) will always be unique, then this is optimal:

PRIMARY KEY (symbol, datetime_utc)

If you cannot guarantee uniqueness, then do

id INT UNSIGNED AUTO_INCREMENT NOT NULL,
PRIMARY KEY (symbol, datetime_utc, id),
INDEX(id)

(Change INT to BIGINT if you might eventually have more than 4 Billion rows.)

This is puzzling:

WHERE    datetime_utc ...
ORDER BY creation_time

It would be faster and usually give the same results (maybe even better results) to us the same column for both:

WHERE    datetime_utc ...
ORDER BY datetime_utc

If you expect to have billions of rows, you should shrink each datatype as much as practical.

  • Do you also have a volume column? Some of the indexes won't fit in an INT.
  • Normalize symbol and replace it with, perhaps, SMALLINT UNSIGNED (2 bytes, up to 64K values) or something similar.
  • Values for open, etc won't fit in some smaller datatypes for BRK.A.
  • Penny stocks may need more than 2 decimal places.
  • Old readings were in fractions, with the denominator being a power of 2.
  • Caveat: The above advice is based on US markets; other markets may have more issues.

The name daily_price_history implies one row per symbol per day? Yet the use of an 8-byte DATETIME(6) implies you are recording every trade. (DATE is only 3 bytes.)

After making some changes, please provide SHOW CREATE TABLE, the main queries, and answers to my questions. I may have more advice.

user_17668310
Related