"Filter inputs per column" does not use index to search

[jessie-github]

Details

In the Data|Grid view, it seems that filtering data using the "Filter inputs per column" (and "Filter by text") is very slow because it is not using the database indexes.

Steps to reproduce

1. Take a big database with an index on column user_id
2. Go to the Data|Grid view and select the filter "Filter by SQL expression"
3. Filter using "user_id = "12345" and we will get the result immediately
4. select the filter "Filter by text" and select the option "Filter inputs per column"
5. In the user_id column search field, enter "12345" and the result will be very slow

Operating system

Windows 10

SQLiteStudio version

SQLiteStudio v3.3.3.

[pawelsalawa]

It is because with filter per column, SQLiteStudio uses columnName LIKE '%filterValue%', because this is usually what people look for - i.e. cells containing filter value, not just exactly equal to it.

The LIKE operator is known to be significantly slower than = and this is especially true when it comes to indexed columns. If you need to match exact values, then the tool for you is the single filter on top with SQL expression.

[jessie-github]

It would be nice to have an option to be able to override this behavior ("equal" instead of "like"). Otherwise we cannot really use the "filter inputs per column" with very big database.

Thanks

[pawelsalawa]

Created #4307