0

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?

Angel Vega
  • 11
  • 2
  • 1
    Indexes should be added to resolve queries. We can't tell you what indexes to add if we don't know your data access patterns. – Tom V Jan 22 '20 at 09:50
  • 1
    ... + 99% that separate indices by ENTRY_ID and by USER_ID are excess. – Akina Jan 22 '20 at 10:12
  • 1
    Which DBMS product are you using? Indexing capabilities and usage depends highly on the database product. –  Jan 22 '20 at 10:56
  • 2
    In general you can remove all (non-unique) single column indexes where that column is a leading column in another index –  Jan 22 '20 at 11:01
  • Im currently using Postgresql – Angel Vega Jan 22 '20 at 22:04

1 Answers1

1

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.

Andy E
  • 11
  • 1