How to order multiple index
I have a index with multiple fields, i want to order them by company_id at first, but when i'm looking at my BD, it doesn't appear in first index :
here is the result :
pricings idx_pricing_company_id NRICHER_PRIO_COMPETITOR, CATEGORY_FINAL, GROUP_CONCURRENT, GROUP_MATRIX_GROUP, BRAND, E_TRUSTED, company_id, MODEL, likeStatus, TOP_PRODUCT
pricings pricings_pkey id
3 Replies
Hey @Guillaume630 👋
but when i'm looking at my BDWhat does BD mean? If I understand correctly, you want that the company_id should be the first column in the generated index? Like this:
Yes, exactly!
The problem I have is that I need to display 1 million rows divided by 100 rows per page.
I have one table called Pricing with 3 million rows that should be filtered by company_id (one company contains 1 million rows). So the main filter is company_id.
Then, for this batch of 1 million rows, I need all the distinct values for the specified columns (CATEGORY_FINAL, GROUP_CONCURRENT, etc.) to display them in my filters on the frontend. The goal is to have all the filters contained within the 1 million rows.
I experience a lot of latency when fetching the data, probably because I need to get distinct values based on company_id on a large dataset.
My idea was to create the first index by company_id, then the rest of the filter columns.
So my question is: how should I sort the index as I did in my @@index([company_id, ...])?
OR should I have one index for company_id and another multiple index with all filters?
OR should I have a one-to-many relation (company <=> products) and index all the filters?
Generally, you should order the fields based on how often they are used in your queries. If
company_id is frequently used in your queries, it makes sense to have it as the first field in your index. I would recommend that you have one index specific for company_id.
And the other index with all the filters.
After that, you should also check the query execution plan of the query to confirm that the index are indeeed being used.