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 :
@@index([company_id, CATEGORY_FINAL, GROUP_MATRIX_GROUP, TOP_PRODUCT, NRICHER_PRIO_COMPETITOR, BRAND, GROUP_CONCURRENT, MODEL, E_TRUSTED, likeStatus], name: "idx_pricing_company_id")
@@map("pricings")
@@index([company_id, CATEGORY_FINAL, GROUP_MATRIX_GROUP, TOP_PRODUCT, NRICHER_PRIO_COMPETITOR, BRAND, GROUP_CONCURRENT, MODEL, E_TRUSTED, likeStatus], name: "idx_pricing_company_id")
@@map("pricings")
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
Nurul
Nurul2mo ago
Hey @Guillaume630 👋
but when i'm looking at my BD
What does BD mean? If I understand correctly, you want that the company_id should be the first column in the generated index? Like this:
pricings idx_pricing_company_id company_id, NRICHER_PRIO_COMPETITOR, CATEGORY_FINAL, GROUP_CONCURRENT, GROUP_MATRIX_GROUP, BRAND, E_TRUSTED, MODEL, likeStatus, TOP_PRODUCT
pricings pricings_pkey id
pricings idx_pricing_company_id company_id, NRICHER_PRIO_COMPETITOR, CATEGORY_FINAL, GROUP_CONCURRENT, GROUP_MATRIX_GROUP, BRAND, E_TRUSTED, MODEL, likeStatus, TOP_PRODUCT
pricings pricings_pkey id
Guillaume630
Guillaume6302mo ago
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?
No description
No description
Nurul
Nurul2mo ago
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.