I have index with bilions of rows. In every row there is two very low-selectivity fields (integers) - country and city. Each country and city may be present in 20-30% of rows.
I know two approaches for storing data.
First - create interger fields and search by them as by attributes.
Second - create full-text field (
geo) and encode this values there, like ‘co12 ci234’ and search using MATCH.
So question is: what approach is better for performance for selects like this:
select id, attr1, attr2 from index1 where country = 12 group by attr1 order by attr2 asc limit 100;
select id, attr1, attr2 from index1 where match(’@geo co12’) group by attr1 order by attr2 asc limit 100;
I’m not sure what is better - full index scan or full-text match 30% rows and then search them in index.