如果我有一个像下面的语句那样的select语句,索引中应该包含哪些顺序和哪些列?
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;
需要注意的事项:
>基准表将有数十亿行
> MyCurrencyPairs表是一个本地表,其记录少于10条
> IN_BeginningTime是输入参数
>列Currency1和Currency2是VARCHAR
>列BenchmarkID和BenchmarkType是INT
> Column BenchmarkDateTime是一个日期时间(希望很明显)
我用Currency1,Currency2,BenchmarkType,BenchmarkDateTime和BenchmarkID创建了一个索引,但我没有达到我想要的速度.我可以创建更好的索引吗?
编辑#1:有人要求下面的解释结果.如果还有其他需要,请告诉我
编辑#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);