MySQL – Best Practices for Large Table Indexes

indexindex-tuningMySQL

I have a table which currently contains around 33 millions rows of data, and is constantly recording more rows second by second. The query to pull data out of this table is gradually getting slower and slower, to the point where it is practically unusable.

My table schema is like so:

enter image description here

and the table has the following indexes:

enter image description here

The usual query from this table consists of selecting rows where the shift is equal to X, and the timestamp is between X and Y.

Can anyone provide/suggest a better table set up, or how I can improve upon the indexes to make this table more efficient?

Right now I'm trying to go through and re-index it, as I believe the indexes have become fragmented, and MySQL keeps loosing connection just trying to reindex the table. So I'm not sure how I can even accomplish this. My thought is to re-create the table structure and then slowly migrate over the data using a more efficient index set.

Best Answer

As a direct answer, what your query needs is a composite index on (shift, timestamp): ALTER TABLE historical_data ADD INDEX shift_ts(shift, timestamp)

However, you have a lot of indexes. and some are redundant.

  • tag_id: has low cardinality. Drop it if you don't have "WHERE tag_id=x" queries
  • shift, timestamp: combine them in 1 index (This is useful for your query)
  • hd_multicolumn: drop
  • value: if you don't query it, drop it
  • hd_multicolumn2: drop it
  • [IMPORTANT] If your table is an InnoDB table, add an auto increment field, and make it a primary key
Related Question