Mysql – Does it makes sense to partition on a foreign key

MySQLpartitioning

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.

Extra info:

Table:

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;

Best Answer

FOREIGN KEYS are not supported in PARTITIONed tables. (With luck this might change in 5.8.)

If you don't have over a million rows, don't bother to PARTITION.

If you are not depending on FOREIGN KEY CONSTRAINTS, don't use them. INDEXes will suffice.

If you would like to discuss these further, please provide SHOW CREATE TABLE (even if not finalized) and the main SELECTs.

Based on what you said, I would guess that you need a compound index:

INDEX(OtherID, NegativeTimestamp)

Edit

I see a redundant index on MarketID. Also UNIQUE(ID) is redundant since the PRIMARY KEY is a UNIQUE key.

If the two Close columns have the same information, but one is negated, then the negative on can probably be removed. Then use DESC in the ORDER BY.

The use of @variables in the WHERE may be poorly optimized; let's see EXPLAIN SELECT ....

I would replace all of the indexes with just 2:

PRIMARY KEY (`MarketID`, Close_Datetime, ID),
INDEX(ID),  -- This is sufficient for AUTO_INCREMENT (except for protecting from deliberate dup)
-- nonexistent field?  KEY `OpenTimestamp` (`Open_Datetime`),
-- unneeded?   KEY `CloseTimestamp` (`Close_Datetime`),
-- unneeded?   KEY `CloseNegativeTimestamp` (`Close_NegativeTimestamp`),
-- covered     KEY `MarketIDIndex` (`MarketID`),
-- replaced    KEY (`MarketID`,`Close_NegativeTimestamp`),

Also, I would shrink most of the fields to save space and improve cacheability. BIGINT is not really needed? MEDIUMINT would suffice for MarketID? decimal(30,15) takes 14 bytes, mostly zeros. DOUBLE (8 bytes) might work?

(I am assuming this is Stock Market data?)

If you choose to get rid of the FK and go with PARTITION, then I would need to see the other important SELECTs.

But, for that matter, do you need ID? Can't (MarketID, CloseDate) be the unique PRIMARY KEY? Note: It would be a 3-byte DATE, not a 5 or 8-byte DATETIME.

Related Question