How to speed up queries in MySQL on a table with ~50 million rows

Question

The table has 4 columns of type datetime, smallint(6), tinyint(4) and decimal(15,5). The first 3 columns are PK. There are four indexes on the table: one with all the PK columns, and one with each column separately. The table has ~50 Million rows and grows rapidly, the plan is to keep it under 100 million rows by archiving the oldest data. Reports based on various datetime ranges and other combinations of the PK columns are run on it frequently, but these reports have begun to take too long. Around 5 minutes to return 10 records.

Column Names

DateTimeStamp--DateTime------PK,NN

SystemNumber--SmallInt(6)------PK,NN

DataNumber------TinyInt(4)-------PK, NN

Value---------------Decimal(15, 5)

Indexes

Primary-------------------BTREE-Unique---DateTimeStamp, DataNumber, SystemNumber, value

DateTimeIDX------------BTREE-Non Unique--DateTimeStamp

DataNumber-------------BTREE-Non Unique---DataNumber

SystemNumberIDX----BTREE--Non Unique---SystemNumber

Select  DateTimeStamp, Value
    From  SystemStats
    WHERE  DataType=16
      AND  DateTimeStamp >='" & StartDate & "'
      and  DateTimeStamp <= '" & EndDate & "'
      and  SystemNumber =" & mVars.SystemNumber & "
    Order by  DateTimeStamp;

My Question

---

How would you change the index on the table to speed up queries?

Answer 1

Provide SHOW CREATE TABLE; what you provided is incomplete. 4 indexes on 50M rows is likely to slow down INSERTs; we need to discuss that.

For that one query, you need the one composite index:

(DataType, SystemNumber, DateTimeStamp) -- or
(SystemNumber, DataType, DateTimeStamp) -- (but not both)

The order is important. A "composite" index is not the same as indexes on each column. One of those could (should) probably be the PRIMARY KEY instead of the one you mentioned.

More discussion on creating the best index: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

If you have other queries; let's see them. Else get rid of the rest of the indexes.

Perhaps you meant "<" instead of "<="? (BETWEEN is inclusive.)

Is there some reason for using DECIMAL(15,5) instead of, say, FLOAT (which takes 4 bytes (float) versus 8 and still gives 6-7 significant digits of precision.)

Is the table InnoDB? This is important because of the way the PK works.

You have PRIMARY KEY(DateTimeStamp, DataNumber, SystemNumber, value); does that mean you expect to have multiple values for each date+num+sys combination? (It is unusual to have something called "value" included in a PK.)