How to optimize a MySQL query which is taking 10 sec to fetch results

I have a MySQL database. I have a table in it which has around 200000 rows.

I am querying through this table to fetch the latest data.Query

select *
from `db`.`Data`
where
     floor = "floor_value" and
     date = "date_value" and
     timestamp > "time_value"
order by
    timestamp DESC
limit 1

It is taking about 9 sec to fetch the data, when the number of rows in the table were less, it did not take this long to fetch the data. Can anyone help me with how do I reduce the time taken for the query?

Answer

Try adding the following compound index:

CREATE INDEX idx ON Data (floor, date, timestamp);

This index should cover the entire WHERE clause and also ideally should be usable for the ORDER BY clause. The reason why timestamp appears last in the index is that this allows for generating a final set of matching timestamp values by scanning the index. Had we put timestamp first, MySQL might have to seek back to the clustered index to find the set of matching timestamp values.