CODEWITHSUNDEEP
mysqldatedatetimesql-order-byquery-optimization
chink
chink

Reputation: 1643

Making indexes to my sql table for a query with "where" and "order by" clauses

I have a table in a mysql db. My query looks like

SELECT device_id,date,timestamp,power,floor 
from tableData 
where date=%s 
and floor=%s 
order by timestamp desc 
limit 1

I have created index on my table for quicker results . My indexes:

+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| minionData |          1 | idx1     |            1 | date        | A         |        9447 |     NULL | NULL   | YES  | BTREE      |         |               |
| minionData |          1 | idx1     |            2 | floor       | A         |       12072 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

So my question is for the particular query I am using, indexes on data and floor columns are good enough or should i index on timestamp column also, for increasing the query performance?

Upvotes: 3

Views: 243

Answers (2)

Mark Sirleaf
Mark Sirleaf

Reputation: 11

Adding an index to each single column doesn't guarantee database optimization, timestamp maybe used for sorting or filtering.

Just use tableData(date, floor)

Upvotes: 1

GMB
GMB

Reputation: 222432

Adding an index to each single column does not allow the database to properly optimize the query (only one index will be used at a time).

To start with: to optimize the where clause, you would need a compound index on both columns, like:

tableData(date, floor)

But to make this even more efficient, you could also add the column that is used for sorting in the ìndex, so: 

tableData(date, floor, timestamp)

If you are running MySQL 8.0, you can make the index descending on column timestamp:

tableData(date, floor, timestamp desc)

Finally: one option would be to add the other columns that you select in the index, to make it a covering index; this could allow the entire query to be executed using the index only (ie without looking at the table data):

tableData(date, floor, timestamp desc, device_id, power)

For many more information on how to optimize the use of indexes in MySQL, I would recommend reading the great MySQL index cookbook written by Rick James (which is also an active SO user).

Upvotes: 4

Related Questions