How should i index this? i always had this question and i hope you can help me with this, how should i index created_at if i need to order results based on date.

Example:

COMMENTS TABLE:


ID (UUID)
ENTRY_ID (UUID)
USER_ID (UUID)
CONTENT
FEATURED
STATUS
CREATED_AT


I have this indexes:


PRIMARY: ID
INDEX: ENTRY_ID
INDEX: ENTRY_ID | CREATED_AT (to get latest entry comments)
INDEX: USER_ID
INDEX: USER_ID | CREATED_AT (to get latest user comments)
INDEX: STATUS | CREATED_AT (to get comments marked as spam ordered by older first)
INDEX: CREATED_AT (to get latest comments in entire db)


Is it right or i can skip some indexes?
You don't need single column indexes on fields that are also the first column in multi column indexes.

However you shouldn't specify the column order just based on that. For best performance, you want the column that has the highest cardinality (ie "uniqueness") first in the column order.

I think that you should just get rid of the CREATED_AT single column index and keep the other single column ones.

The 3 double column indexes should all  have the CREATED_AT field first ordered DESC for maximum efficiency.