Redundant MySQL Index?
Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No TickerID 23200 A
Timestamp 13897209 A
Edit Drop TickerID BTREE No No TickerID 11737 A
Edit Drop Timestamp BTREE No No Timestamp 18 A
~99,424,209 InnoDB utf8_general_ci 5.1 GiB
OK before I try to "optimize" this DB by deleting unneeded indexes I thought I'd ask here. This table gets hit a lot so I want to speed up insert performance. I read 5 articles on the net that seem to indicate that the single index on TickerID is redundant because the multiple index (TickerID,Timestamp) will be used if I ever run a query on just tickerID.
Occasionally, I will like to do EOD reporting, so that I might just do something like SELECT * WHERE Timestamp > Today() 开发者_开发知识库- 1 day
or something like that. Do I need the timestamp index as well?
This index on tickerId, timestamp
can be used by all queries which can use an index on tickerId
only.
However, for a query like that:
SELECT *
FROM mytable
WHERE Timestamp > Today() - 1
an index on (tickerId, timestamp
) will not be used, since there is no equality filter on tickerId
.
For this query, you should create an index on timestamp
only or rewrite thу query:
SELECT m.*
FROM (
SELECT DISTINCT tickerId
FROM mytable
) md
JOIN mytable m
ON m.tickerId = md.tickerId
AND m.timestamp > TODAY() - 1
However, the latter query is less efficient, especially if you have lots of distinct values in tickerId
.
精彩评论