0

SHOW CREATE TABLE meter_data returns - 

CREATE TABLE `meter_data` (
`DeviceID` varchar(45) NOT NULL,
`MeterID` int(11) NOT NULL,
`TimeStamp` int(11) NOT NULL,
`W` float NOT NULL,
`F` float NOT NULL,
`PF1` float NOT NULL,
`V1` float NOT NULL,
`A1` float NOT NULL,
`PF2` float NOT NULL,
`A2` float NOT NULL,
`PF3` float NOT NULL,
`A3` float NOT NULL,
`FwdHr` float NOT NULL,
 PRIMARY KEY (`DeviceID`,`MeterID`,`TimeStamp`),
 KEY `MeterID` (`MeterID`,`DeviceID`,`TimeStamp`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

whats wrong with it?

Inderpal Singh
  • 270
  • 2
  • 8
  • 24
  • This link has a great answer on how composite indices work: http://stackoverflow.com/questions/1823685/when-should-i-use-a-composite-index. There's nothing wrong with your keys, you just may need to add some to handle all the queries you are running. – Tom Jun 13 '13 at 13:27
  • If you are not using all the fields in the compound index, or the first X (1, 2, 3, etc.) fields, the query will not use the index. If you want to filter on DeviceID and TimeStamp, you would need another compound index for it. The ones you have won't handle this criteria. – Tom Jun 13 '13 at 13:29

1 Answers1

1

From MySQL doc: "MySQL cannot use the index to perform lookups if the columns do not start form a leftmost prefix of the index."

The problem is not when not all parts of the composite key are included in the WHERE clause but when the columns don't start from the leftmost index column.

E.g. if you have index(col1, col2, col3) in which case the leftmost index column is col1,

then

WHERE col1 = x AND col2 = y and WHERE col1 = x AND col2 = y AND col3 = z

WILL use the index, but

WHERE col2 = x AND col3 = y

will NOT.

This is because the index is created by concatenating the column values so if col1=x, col2=y and col3=z then the index value will be x-y-z. In the last WHERE clause MySQL cannot look at the indices because the first (leftmost) part of the index isn't used.

In your case, using either (MeterID,DeviceID) or (MeterID,DeviceID,TimeStamp) in the WHERE clause will result in indexed search, but e.g. (DeviceID,TimeStamp) or (DeviceID) or (TimeStamp) will result in table scan.

marekful
  • 14,986
  • 6
  • 37
  • 59
  • can you please tell me command for dropping this index and creating new one – Inderpal Singh Jun 13 '13 at 13:48
  • To remove, use `ALTER TABLE tbl1 DROP PRIMARY KEY` and to re-add, use `ALTER TABLE tbl1 ADD PRIMARY KEY(col1, col2, ...)`. Documentation: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html – marekful Jun 13 '13 at 13:52