Geek asks and answers

Index Columns and Order

If I have a select statement, such as the statement below, what order and which columns should be included in the index?

SELECT MIN(BenchmarkID),
       MIN(BenchmarkDateTime),
       Currency1,
       Currency2,
       BenchmarkType
FROM Benchmark
       INNER JOIN MyCurrencyPairs ON Currency1 = Pair1
                            AND Currency2 = Pair2
WHERE BenchmarkDateTime > IN_BeginningTime
GROUP BY Currency1, Currency2, BenchmarkType;

Notes:

  • Benchmark table will contain billions of rows.
  • The MyCurrencyPairs table is a local table that will have less than 10 records.
  • IN_BeginningTime is an input parameter
  • The columns Currency1 and Currency2 are VARCHAR
  • BenchmarkID and BenchmarkType columns are INTs
  • BenchmarkDateTime column is a date-time column (I hope it was obvious)

I created an index with Currency1, Currency2, BenchmarkType, BenchmarkDateTime and BenchmarkID, but I did not get the speed that I wanted. Can I create a better index?

Edit # 1: Someone requested the results of an explanation below. Let me know if anything else is needed.

enter image description here

№2: - DDL ( , create) :

( )

CREATE TABLE `benchmark` (
    `SequenceNumber` INT(11) NOT NULL,
    `BenchmarkType` TINYINT(3) UNSIGNED NOT NULL,
    `BenchmarkDateTime` DATETIME NOT NULL,
    `Identifier` CHAR(6) NOT NULL,
    `Currency1` CHAR(3) NULL DEFAULT NULL,
    `Currency2` CHAR(3) NULL DEFAULT NULL,
    `AvgBMBid` DECIMAL(18,9) NOT NULL,
    `AvgBMOffer` DECIMAL(18,9) NOT NULL,
    `AvgBMMid` DECIMAL(18,9) NOT NULL,
    `MedianBMBid` DECIMAL(18,9) NOT NULL,
    `MedianBMOffer` DECIMAL(18,9) NOT NULL,
    `OpenBMBid` DECIMAL(18,9) NOT NULL,
    `ClosingBMBid` DECIMAL(18,9) NOT NULL,
    `ClosingBMOffer` DECIMAL(18,9) NOT NULL,
    `ClosingBMMid` DECIMAL(18,9) NOT NULL,
    `LowBMBid` DECIMAL(18,9) NOT NULL,
    `HighBMOffer` DECIMAL(18,9) NOT NULL,
    `BMRange` DECIMAL(18,9) NOT NULL,
    `BenchmarkId` INT(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`BenchmarkId`),
    INDEX `NextBenchmarkIndex01` (`Currency1`, `Currency2`, `BenchmarkType`),
    INDEX `NextBenchmarkIndex02` (`BenchmarkDateTime`, `Currency1`, `Currency2`, `BenchmarkType`, `BenchmarkId`),
    INDEX `BenchmarkOptimization` (`BenchmarkType`, `BenchmarkDateTime`, `Currency1`, `Currency2`)
)

( MyCurrencyPairs )

CREATE TEMPORARY TABLE MyCurrencyPairs
    (
        Pair1 VARCHAR(50),
        Pair2 VARCHAR(50)
    ) ENGINE=memory;
  CREATE INDEX IDX_MyCurrencyPairs ON MyCurrencyPairs (Pair1, Pair2);
+5
2

BenchMarkDateTime .

: , .

-, By .

, "=" " > ", .

+1

, MySQL . MyCurrencyPairs , MIN(BenchmarkId), BenchmarkDateTime. , .

:

ALTER TABLE benchmark
  ADD KEY `IDX1` (
    `Currency1`,
    `Currency2`,
    `BenchmarkType`,
    `BenchmarkDateTime`
  ),
  ADD KEY `IDX2` (
    `Currency1`,
    `Currency2`,
    `BenchmarkType`,
    `BenchmarkId`,
    `BenchmarkDateTime`
  );

SELECT
  (
    SELECT
      BenchmarkId
    FROM
      benchmark FORCE KEY (IDX2)
    WHERE
      Currency1 = ob.Currency1 AND
      Currency2 = ob.Currency2 AND
      BenchmarkType = ob.BenchmarkType
      AND BenchmarkDateTime > IN_BeginningTime
    ORDER BY
      Currency1, Currency2, BenchmarkType, BenchmarkId
    LIMIT 1
  ) AS BenchmarkId
  ob.*
FROM
  (
    SELECT
      MIN(BenchmarkDateTime),
      Currency1,
      Currency2,
      BenchmarkType
    FROM
      benchmark
    WHERE
      BenchmarkDateTime > IN_BeginningTime
    GROUP BY
      Currency1, Currency2, BenchmarkType
  ) AS ob
INNER JOIN
  MyCurrencyPairs ON Currency1 = Pair1 AND Currency2 = Pair2;

, GROUP BY . , Currency1, Currency2, BenchmarkType, , MyCurrencyPairs, , , MySQL , . IDX1, .

- MIN(BenchmarkId) . IDX2, . FORCE KEY (IDX2) Currency1, Currency2 BenchmarkType ORDER -part , MySQL . , . , , , .

, ( ):

+----+--------------------+-----------------+-------+---------+------+---------------------------------------+
| id | select_type        | table           | type  | key_len | rows | Extra                                 |
+----+--------------------+-----------------+-------+---------+------+---------------------------------------+
|  1 | PRIMARY            | <derived3>      | ALL   | NULL    | 1809 |                                       |
|  1 | PRIMARY            | MyCurrencyPairs | ref   | 106     |    2 | Using where                           |
|  3 | DERIVED            | benchmark       | range | 17      | 1225 | Using where; Using index for group-by |
|  2 | DEPENDENT SUBQUERY | benchmark       | ref   | 9       |  520 | Using where; Using index              |
+----+--------------------+-----------------+-------+---------+------+---------------------------------------+

, , , .

, 20 (1.07s 0,05s), 1,2 , , , , YMMV.

0

More articles:


All Articles