database - Querying large not indexed tables -

We are developing a CRUD such as the web interface for web applications. For this, we need to show data from different tables, some huge and very "alive", with many rows (millions). There are some small, configurable tables

Now we want to allow our users to filter, refine, sort, endorsement etc. on the grid. As a result of user selection - We are creating selected questions.

For obvious reasons, filtering on indexed fields will produce a long running query On the other hand, indexing each column of a table, looks a bit "weird" and we have more than 50 rows There are tables with

We are looking at Apache Lusen, but as far as I understand - this text helps to solve the indexing well. But what about numbers, dates, categories? Is there a solution, available discussions for that issue?

In addition to this, I should say that this problem is only UX specific for all the requirements of our applications, we do good .

You are right, in general, you do not want to allow random predictions on non-indexed areas. Although, how much effect it is, this table size, database engine is being used and the machine is being used to drive the database. Some engines are not very bad with non-indexed columns, but in the worst case, each sequential scan will be incomplete on some suggestions

1. Check using the Column Store database engine, store these store data Row wise instead of callwheel, which can be very fast for random future on non-indexed columns. Column stores are not a universal solution, although you often need all the fields on one line
2. The index of the main columns that are asked by users and indicate in the UX layer that queries on some columns are slow Will go Users will accept more, especially if they already know that the query in the column will be slowed
3. If possible, just throw the memory on it engine like Oracle or SQL / Server will be great, while most of your The database will fit in the memory. The only problem is that once you have exceeded your database memory performance, a rock will fall (without warning)
4. If possible, consider using vertical partitions. This allows you to split a line into 2 or more pieces of storage, which can reduce IO's forecast.
5. Make sure you know it, but make sure the columns used for joins are indexed.