In our application we have a page were we list our entities with many options to filter and to search. Over time this page got slower and slower especially for large datasets, so we thought about how to improve the performance. I have two ideas:
- Use a service like Elasticsearch which is built for problems like these (though we would not like to introduce an additional dependency)
- Implement proper caching
However caching only provides a speedup if the same resource is requested twice or more. On a page where records can be filtered in many different ways however I don't believe that the speedup of caching will come into use often because of the huge variety with which the data will be filtered and presented. And even if a user is requesting the page using identical filters as someone (or himself) before, there is a high probability that until then the data has changed and therefore the cache is invalid again.
Am I wrong in my assumption? How to develop pages like these in a performant way?
Thanks
EDIT:
Here some more information:
We are using Ruby on Rails, a MySQL database and the ActiveRecord ORM. For one request of the mentioned page we fetch data from about 10 different database tables with each containing between 5000 and 5,000,000 entries. There is rarely text-based searching, most of the time we search by filtering foreign keys as in "Give me all employees of company XY". Our database is properly indexed, about this we took great care. I furthermore did a thorough analysis on the queries using the MySQL EXPLAIN functionality. Filtering and preparing the data and meta information (e.g. record counts) happens on the server-side.
The site performs poorly because it displays a lot of information and therefore has to execute several heavy queries. As an example for a heavy query:
Get all rooms for a certain building where at least one person is sitting. In order to get this data we have to first fetch all people of this building and then get the count of people for each room to see whether there is at least one person in it.