Index Columns and Order

Question

If I have a select statement like the statement below, what order and what columns should be included in an 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;

Items to note:

• The Benchmark table will have billions of rows
• The MyCurrencyPairs table is a local table that will have less than 10 records
• IN_BeginningTime is a input parameter
• Columns Currency1 and Currency2 are VARCHARs
• Columns BenchmarkID and BenchmarkType are INTs
• Column BenchmarkDateTime is a datetime (hopefully that was obvious)

I've created an index with Currency1, Currency2, BenchmarkType, BenchmarkDateTime, and BenchmarkID but I wasn't getting the speed I was wanting. Could I create a better index?

---

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

---

Edit #2: Someone requested the DDL (I'm assuming this is the create statement) for the two tables:

(this benchmark table exists in the database)

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`)
)

(I'm creating the MyCurrencyPairs table in my routine)

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

Answer 1

BenchMarkDateTime should be the first column in your index.

The rule is, if you use only a part of a composite index, the used part should be the leading part.

Secondly, the Group By should match an index.

Your performance would be better if some how you can make your query use "=" instead of ">" which is a range check query.