I am trying to enhance the search performance of a table based on whether certain columns contain values.
I have the following columns :
id | link | text | date | lat | long | type
If a record has a value in lat & long it can be shown on a map. It’s considered a “dot”
If a record has a value in date & lat & long it is considered an “event”
If a record has a value in type and text it is considered “news”
With over 1 million records in the table, what is the best way to index so I can quickly extract all “dots” or all “events” or all “news”?
To reiterate, I don’t care to index the actual values of these columns, only if there is ANY value in that column.
I tried creating an index of multiple columns but it used the values of the columns, and so many often unique values resulted in slow performance.
I considered using a “hash” based on which columns were not null, but I couldn’t extract those 3 variations beaded on the hash result.
Any advice appreciated.