I'm trying to optimize a ~100GB database. All data values are in one single innodb table, is this bad? Here's what a common query looks like:

SELECT `CensorCode`, `LocalDateTime`, `UTCOffset`, `DateTimeUTC`, `MethodID`, `SourceID`, `QualityControlLevelID`, `DataValue`, `OffsetTypeID`, `OffsetValue`, `d`.`SampleID`, `s`.`LabSampleCode`, `d`.`QualifierID`, `q`.`QualifierCode`
FROM (`datavalues` d)
LEFT JOIN `samples` s ON `d`.`SampleID` = `s`.`SampleID`
LEFT JOIN `qualifiers` q ON `d`.`QualifierID` = `q`.`QualifierID`
WHERE `d`.`SiteID` =  '15'
AND `d`.`VariableID` =  '8'
AND `d`.`LocalDateTime` >= '2009-04-18T00:02'
AND `d`.`LocalDateTime` <= '2009-04-20T00:02'

Here's what the explain statement on the above query looks like:

| id | select_type | table | type        | possible_keys                               | key                                         | key_len | ref               | rows    | Extra                                                                     |
|  1 | SIMPLE      | d     | index_merge | FK_DataValues_Variables,FK_DataValues_Sites | FK_DataValues_Sites,FK_DataValues_Variables | 4,4     | NULL              | 1390435 | Using intersect(FK_DataValues_Sites,FK_DataValues_Variables); Using where |
|  1 | SIMPLE      | s     | eq_ref      | PRIMARY                                     | PRIMARY                                     | 4       | odm.d.SampleID    |       1 |                                                                           |
|  1 | SIMPLE      | q     | eq_ref      | PRIMARY                                     | PRIMARY                                     | 4       | odm.d.QualifierID |       1 |   

Does this mean it's using indexes? If not, could someone explain how to create an index, I don't understand what it means. Any other suggestions would be greatly appreciated. Thanks in advance.

Also, here is the output from SHOW CREATE TABLE

| datavalues | CREATE TABLE `datavalues` (
  `DataValue` double NOT NULL,
  `ValueAccuracy` double DEFAULT NULL,
  `LocalDateTime` datetime NOT NULL,
  `UTCOffset` double NOT NULL,
  `DateTimeUTC` datetime NOT NULL,
  `SiteID` int(11) NOT NULL,
  `VariableID` int(11) NOT NULL,
  `OffsetValue` double DEFAULT NULL,
  `OffsetTypeID` int(11) DEFAULT NULL,
  `CensorCode` varchar(50) NOT NULL DEFAULT 'nc',
  `QualifierID` int(11) DEFAULT NULL,
  `MethodID` int(11) NOT NULL DEFAULT '0',
  `SourceID` int(11) NOT NULL,
  `SampleID` int(11) DEFAULT NULL,
  `DerivedFromID` int(11) DEFAULT NULL,
  `QualityControlLevelID` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ValueID`),
  UNIQUE KEY `DataValues_UNIQUE_DataValues` (`DataValue`,`ValueAccuracy`,`LocalDateTime`,`UTCOffset`,`DateTimeUTC`,`SiteID`,`VariableID`,`OffsetValue`,`OffsetTypeID`,`CensorCode`,`QualifierID`,`MethodID`,`SourceID`,`SampleID`,`DerivedFromID`,`QualityControlLevelID`),
  KEY `FK_DataValues_Sources` (`SourceID`),
  KEY `FK_DataValues_QualityControlLevels` (`QualityControlLevelID`),
  KEY `FK_DataValues_OffsetTypes` (`OffsetTypeID`),
  KEY `FK_DataValues_CensorCodeCV` (`CensorCode`),
  KEY `FK_DataValues_Variables` (`VariableID`),
  KEY `FK_DataValues_Methods` (`MethodID`),
  KEY `FK_DataValues_Qualifiers` (`QualifierID`),
  KEY `FK_DataValues_Samples` (`SampleID`),
  KEY `FK_DataValues_Sites` (`SiteID`)
  • The query is using 2 indexes on table datavalues with the Index Merge Intersection Access Algorithm – ypercubeᵀᴹ Apr 16 '14 at 6:49
  • A multi-column index on (VariableID, SiteID, LocalDateTime) will probably be more efficient - but it will certainly take some time (for the index) to be built in a 100GB table. – ypercubeᵀᴹ Apr 16 '14 at 6:51
  • Thanks. So do I have to remove the current index and then replace it with a multi-column index? – cherner Apr 16 '14 at 16:11

Question: Does this mean the query is using indexes?

The Using intersect(FK_DataValues_Sites,FK_DataValues_Variables) means that the query is using two indexes on table datavalues with the Index Merge Intersection Access Algorithm and then (Using where) the rows are additionally checked against some other conditions.

Question: Any other suggestions? (I guess about efficiency)

This is most probably not the most efficient way, especially if the query is retrieving a small perecentage of the table's rows.

For example, lets say that the SiteID = 15 condition narrows the search to 10% of the rows and the VariableID = 8 condition narrows the search to 5% of the rows. The two existing indexes can be used invidually or combined (with tthe help of the index merge intersection algorithm) to narrow the search to (0.10 * 0.05 =) 0.5% of the tables' rows (and that is what the MySQL engine is doing in this case.)

With a 280M rows table, this means that the first index can efficiently give us the 28M keys that match the 1st condition and the second index can give us the 14M rows that match the 2nd condition. Then, the index merge algorithm can further narrow it to the 1.4M keys (at the extra cost of this algorithm). Then, the 1.4M rows have to be retrieved and the 3rd condition (LocalDateTime >= '2009-04-18T00:02' AND LocalDateTime <= '2009-04-20T00:02) - that is not covered by any index - has to be checked for every one of these 1.4M rows.

If this 3rd condition narrows the results to, say 5% or 70K rows, the overall efficiency of the query will not be the best possible.

The best would be if there was an index that covered all 3 conditions. Fortunately, there is a way. A multi-column index on either (VariableID, SiteID, LocalDateTime) or on (SiteID, VariableID, LocalDateTime) would be enough to narrow the search to only the needed keys (with the previous example to only 70K) and then only those rows would be retrieved from the table.

Extra question: Do I have to remove the current index and then replace it with a multi-column index?

No, I wouldn't remove any index, just add the new one, unless I was sure I don't need an existing index.

But analyzing queries (and indexing) should be done after examining all the queries against the database and the requirements (which queries need to be extra fast and optimized, which ones we can afford to wait longer for them, how other operations will be affected if we add a lot of indexes, etc.)

  • Thanks for the detailed response. The index (SiteID, VariableID, LocalDateTime) did speed things up a lot. I'm wondering about the ordering of the index a bit more-- there are more SiteIDs than VariableIDs and there are more LocalDateTimes than SiteIDs and VariableIDs. So what I am trying to say is that SiteID narrows down the query the most, then VariableID, then LocalDateTime. Is (SiteID, VariableID, LocalDateTime) then the appropriate index? – cherner Apr 17 '14 at 14:37
  • If your queries have both conditions (WHERE SiteID = SomeConstant AND VariableID = AnotherConstant), then the order of the 2 columns inside the index does not matter. Either of the 2 suggested indexes will be good. Keeping the LocalDateTime in the last part of the index as the condition on that column is a range, not equality. – ypercubeᵀᴹ Apr 17 '14 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.