1

Here is my table:

// mytable
+----+---------+-------------+
| id | user_id |  unix_time  |
+----+---------+-------------+
| 1  | 324435  | 1481199265  |
| 2  | 745645  | 1481194241  |
| 3  | 547346  | 1481291987  |
+----+---------+-------------+

And this is my current composite index:

mytable(user_id, unix_time)

And this is one of my queries:

DELETE FROM mytable WHERE unix_time < unix_timestamp(subdate(now(), interval '1' YEAR))

I've read somewhere:

When there is > or < or BETWEEN or >= or <= operators, then that column should be the last one in the composite index.

So as you see, there is both < operator and unix_time is the last one in my current index (I've created index above because of another query) .. Now I want to know, Either:

  • Do I need to create one more index like mytable(unix_time) to benefit query about

Or

  • Quoted sentence is right and no need to any index else for query above

?

Martin AJ
  • 6,261
  • 8
  • 53
  • 111
  • Well it looks like unix_time is the last field mentioned in your composite index, so I dont understand the question. Are you getting errors? – RiggsFolly Dec 08 '16 at 14:29
  • @RiggsFolly no no, there isn't any error, just I want to know, do I need to make another index or current index it enough ? – Martin AJ Dec 08 '16 at 14:30
  • I am pretty sure you answered your own question. If it works its right – RiggsFolly Dec 08 '16 at 14:31
  • @Bert Yes I do. As I've mentioned in the question, I use this composite index `mytable(user_id, unix_time)` – Martin AJ Dec 08 '16 at 14:31
  • I think the index does not work, check this: http://stackoverflow.com/questions/795031/how-do-composite-indexes-work – Emil Holub Dec 08 '16 at 14:33
  • @RiggsFolly The problem is when I run my query by writing `EXPLAIN` in the beginning of it, `key` column is NULL .. so it doesn't use any index – Martin AJ Dec 08 '16 at 14:33
  • The answers are fine. Going beyond this Q&A, find [_here_](http://mysql.rjweb.org/doc.php/index_cookbook_mysql) more discussion of how to make the 'best' index. – Rick James Dec 09 '16 at 01:03

2 Answers2

1

Do I need to create one more index like mytable(unix_time) to benefit query

Yes. It's not the < vs > or = that is the issue. It's which columns are in the index, and in which order.

If you were doing 

DELETE FROM mytable 
WHERE unix_time < unix_timestamp(subdate(now(), interval '1' YEAR))
    AND user_id=123456

then the index on user_id+unix_time would get used. Also, if your existing index were on unix_time+user_id, instead of user_id+unix_time, then the database could use it.

And yes, this is accurate:

When there is > or < or BETWEEN or >= or <= operators, then that column should be the last one in the composite index.

but in this case, because you were not also selecting on user_id, it didn't matter.

Andy Lester
  • 91,102
  • 13
  • 100
  • 152
1

Only plan of query shows how to run a query. But you offered bad index for that query because unix_time is second field. The good indexes for that query are

mytable(unix_time, user_id)

or

mytable(unix_time)

but only the query plan shows whether or not it will be used.

GrApDev
  • 150
  • 1
  • 17