I have following query
SELECT * FROM mytable WHERE toshow = 1 ORDER BY timestamp DESC
The timestamp column stores current timestamp in database when inserting row. In some rows 'toshow' value is 0 and in some rows its 1
What INDEX should I use to get results faster for any 0 or 1 toshow value and latest row above
I created index (timestamp, toshow) for this but confusing its right or not.
Covering index:
CREATE INDEX idx ON mytable(toshow, timestamp DESC);
You shoul use the where part first as the most selective part so could be easy used by the where clause
index (toshow, timestamp DESC )
the timestamp column is used for provide the info for order by but this happen after the filering for the rows based on where clause
In this way the index It is used both to filter the lines and to obtain the formats for the sorting in your way the index can't be used for where ..