-1
SELECT * FROM table WHERE from_user=? AND to_user=? ORDER BY created_at DESC

created_at is a datetime column.

According to my previous question (here), I learned that you don't need to create a descending index.

Is this index ok if I have billions of records? (notice I'm not specifying a desc index for created_at)

>> from_user_id, to_user_id, created_at
Community
  • 1
  • 1
TIMEX
  • 259,804
  • 351
  • 777
  • 1,080
  • Yes as long as you follow the order of where clause as per the indexing i.e. from left to right the indexing should be taken into account. I have a similar table not billion but few millions and the explain plan shows nice result on similar query. – Abhik Chakraborty Nov 11 '14 at 10:45

1 Answers1

0

IMO having an index on from_user_id, to_user_id, created_at would not help you much.
I don't think you have many records with the same from_user_id, to_user_id, created_at values. (date time with millisecond!)
If my assumption is correct, in the index with such a low cardinality the effectiveness is reduced to a linear search plus wasting storage.

I will suggest having an index on from_user_id, to_user_id, matching your where criteria, the index will be applied before ordering.

In any case you can see the query execution plan to see the query cost, when having these indexes.
(indexing mechanism overview is interesting)

Community
  • 1
  • 1
Mohsen Heydari
  • 7,256
  • 4
  • 31
  • 46