filter table "excel like"
(1) By anonymous on 2022-05-31 16:01:04 [link] [source]
The very common feature of an "autofilter", how is that done for best performance?
I have a RO table, 2million records, 40 columns, each column has 10 distinct values on average, though some columns have just 2 (yes/no) or some have hundred distinct values.
I do not know which column the user chooses first to filter, so to start, it is a fullscan, the dropdownbox is filled with:
SELECT distinct col17 from mytab
then the user filters a value:
CREATE TABLE filterstack0 as SELECT ROWID from mytab where col17=selection17
I want to keep the filtered records, therefore I create a separate table??
When the user has filtered 2 columns on average I have only 1% of the records left
SELECT distinct col33 from mytab natural join filterstack0
CREATE TABLE filterstack1 as SELECT ROWID from mytab natural join filterstack0 where col33=selection33
What is the proper way to do this? Bitmap indices, virtualtables, ready made extensions for this purpose, memory_columns? Thank you very much for pointing me to the right direction.
(If the table mytab is not one table, but a join of many smaller tables, and instead of a user filtering some columns, I have a backtracking alg., what is the best way to "keep" the filtered subset of the records of each of the tables at every recursion depth layer?)
(2) By Larry Brasfield (larrybr) on 2022-05-31 17:39:22 in reply to 1 [source]
You might consider creating a temp table for initial results, then some indexes on expressions involving the columns of that table. These indexes would also be in the temp schema. Or, instead of using the temp schema, you could attach an in-memory DB to hold the initial results and indexes. The initial result table need have no content beyond the rowid and whatever columns may be used for filtering.
As to whether this is "the proper way to do this?": That would depend upon why doing anything more than formulating queries on the original data is not proper. Have you tried that?
I do not understand how recursion is related to your task. That may reflect a vague understanding of what you are trying to do.