I have a table with 4 million records. One of the columns has 3700 distinct values (text). The field values for this column don't get cached when running "re-scan field values". As a result, when I want to filter that column, it takes too long to search for a value.
I looked in the table metabase_fieldvalues and I don't see a row for that specific column as I expected. I concluded there is a limit on the number of distinct values of a column which beyond that the values are not saved in the metabase_fieldvalues.
Is this correct? If so, what is the limit, and can I change it? If not, what can I do?
@flamber I was aware of the 300 limit. But my question is different. The 300 limit is for when you click the filter button on that column and you see all the values. My question was: After clicking the filter button on that column, I type the first few characters (‘bank’ in the above example), and I expect to see a few values that start with ‘bank’. But this takes a long time for my table. If the distinct values were cached (in the metabase_fieldvalues table I assume) this should have taken a fraction of a second.
Are you saying if we have more than 300 distinct values for a column, the table metabase_fieldvalues does not get updated with that data?
@King_Edward
When there’s more than 300 values, then those are not stored in metabase_fieldvalues, since there could be 50k distinct values, and the way it’s stored in the database probably wouldn’t be efficient.
The search does a lookup in your database, not the Metabase application database, so that’s probably the reason for the latency.
This is also the reason for the autocomplete not using “contains”, but “begins with”, since that could lockup large tables.
Not sure if we could handle this differently, but let me ping @sbelak
How we currently handle this is not ideal, but the gist is that it’s a balancing act. Getting all the values is a reasonably expensive query (full scan if no index) which limits us to how often we do it … which is at odds with freshness that you very much need for decent UX. We have some ideas around incremental updates and probabilistic updates (based on inferred change frequency), but no timeline yet.
@sbelak I agree that getting all the values can be expensive. one solution could be that the values are not stored by automatic scan but are stored if initiated from data model (a scan initiated manually for a certain column).
Without cached values, every time the users want to filter the data, the whole table is scanned which is way more expensive.
on the first post