Say I have a table shapes table with columns like
create table shapes (
id int,
region_id int,
clusted_id int,
color varcahr(60),
edges int,
x int, y int, z int,
orientation int,
created_at date,
updated_at date,
visible tinyint
...
);
I need to create an "advanced search" functionality to query this table that has more than 2M rows. Users can query based on a mix and match of all the available columns. Something like
select * from shapes where created_at between ? and ? and x+y < ? and region_id in (?, ?, ?) ...
There can be fields that appear in the where clause or fields that do not appear. That's the "advanced" search. Also, nothing is full-text. Assume everything is either int, date, boolean or enum.
- Assuming the shapes are distributed evenly throughout all the possible values for all the columns, what are the best practices to index this table?
- Do I have to index all the subsets of the fields in the table together?
- Are there other external services that could help improve the performance? like elasticsearch?
Thank you!
