1

I have 9 columns in my table homes_info table

  1. home_id
  2. location_id (foreign key from locations table)(int)
  3. price (int)
  4. floors(int)
  5. rooms(int)
  6. rental income (float)
  7. payback (float)
  8. offer type (varchar)
  9. date_published

On front-end, i provide many input fields (filters) which allow users to do a select query on one or more than one column in every possible combination they can also sort the results by any column.

so the query can be as complicated as the following query

SELECT * FROM homes_info LEFT JOIN location on locations.location_id = homes_info.location_id WHERE locations.location_name = 'herford' && price < 3000 && price > 1000 && floors < 3 && floors > 1 && rooms < 3 && rooms > 1 && rental income < 100 && rental_income > 150 && payback < 900 && payback > 100 && offer_type = 'rent' ORDER BY any_column_user_want

and the query can be as simple as the following

SELECT * FROM homes_info LEFT JOIN location on locations.location_id = homes_info.location_id WHERE floors > 1 ORDER BY any_column_user_want

And a user can do any possible query between above two extreme queries

So how should I use indexes on above 7 columns, should I give a different index to each column (except home_id and location_id)?

ALTER TABLE homes_info ADD INDEX(price)

ALTER TABLE homes_info ADD INDEX(floors)

ALTER TABLE homes_info ADD INDEX(rooms)

..................................

ALTER TABLE homes_info ADD INDEX(published_date)

but that will make MySQL do merge indexes for bigger queries, I am not sure how bad that is?

to avoid that I can use composite indexes

ALTER TABLE homes_info ADD INDEX( price, floors, rooms, rental income, payback, offer type, published_date)

ALTER TABLE homes_info ADD INDEX(floors, rooms, rental income, payback, offer type, published_date)

ALTER TABLE homes_info ADD INDEX(floors, rooms, rental income, payback, offer type, published_date)

..................................................

..................................................

ALTER TABLE homes_info ADD INDEX(published_date)

So which method would be better? use a different index on each column or use composite indexes?

user9050678
  • 323
  • 1
  • 3
  • 13
  • I can't quote an authoritative source for it being 'better', but separate indexes seems like the obvious thing to do – Chris Lear Sep 04 '18 at 16:07
  • @ChrisLear Thanks, i am already using separate indexes as it was a natural choice, but i thought using composite indexes would increase the speed, that's why I wanted to confirm – user9050678 Sep 04 '18 at 16:17
  • @ChrisLear oh, sry, actually composite index is not actually practical for all the combinations what if I want to select where price = "x" and published_date = "xxxxx"`, in that case, no index will apply because the composite index can work on left most prefix columns – user9050678 Sep 04 '18 at 16:37

0 Answers0