I have a MySQL table which looks a bit like this:
ID (BIGINT, PK), OtherID (INT, FK, indexed), NegativeTimestamp (BIGINT, indexed)
Selects should go very fast, updates almost never happens, inserts are not so frequent.
Queries will always query data within the same OtherID. The other filter will always be a range of
NegativeTimestamp (which is a negative timestamp, so I can use ASC index while I always need the timestamps to be DESC), or the top (for example top 1000 rows with lowest timestamps). Recent data (lowest
NegativeTimestamps) are requested most frequently, but there is no clear line which timestamps are more frequently requested than others.
I'm not sure if I should partition on OtherID (as queries will then never involve two partitions), or on Timestamp, or on none of them.
Any other optimizations are always welcome of course. If possible, also explain why so I can use this information in the future.
CREATE TABLE `myTable` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `MarketID` int(11) NOT NULL, `Close_Datetime` datetime NOT NULL, `Data` decimal(30,15) DEFAULT NULL, `Close_NegativeTimestamp` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), UNIQUE KEY `ID` (`ID`), KEY `IX_FK_MarketPriceCandle` (`MarketID`), KEY `OpenTimestamp` (`Open_Datetime`), KEY `CloseTimestamp` (`Close_Datetime`), KEY `CloseNegativeTimestamp` (`Close_NegativeTimestamp`), KEY `MarketIDIndex` (`MarketID`), KEY `CloseNegativeTimestampMarketID` (`MarketID`,`Close_NegativeTimestamp`), CONSTRAINT `FK_MarketMyTable` FOREIGN KEY (`MarketID`) REFERENCES `markets` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=110011638 DEFAULT CHARSET=utf8;
This table has currently more than 100 million rows and will continue to grow.
Most executed query:
SELECT * FROM myTable p WHERE p.MarketID = @marketID AND p.Close_NegativeTimestamp <= @start AND p.Close_NegativeTimestamp >= @end ORDER BY p.Close_NegativeTimestamp ASC;