Performance on highly filterable web pages

Question

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:

1. Use a service like Elasticsearch which is built for problems like these (though we would not like to introduce an additional dependency)
2. 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.

Answer 1

First of all, caching is hard. You can get some amazing performance improvements when it's implemented correctly and the cache hit ratio is high enough, but you'll gnash a lot of teeth to get there.

Look at this as "just another optimization problem" and proceed accordingly.

This means, don't guess. Find a way to measure the performance of your application and prove conclusively which parts are causing pain. Since (I assume) this is a web app, I'd start by hitting F12 and using the profiler.

Initial questions to ask:

• Is the web browser taking a long time to process the data once it has it?
• Is it taking too long to get data to the browser?
• Is the server taking to long to obtain the data?

Answers to these questions will dictate your next set of questions which will dictate the next (an so on).

Answer 2

Put an index on your database.

Databases are good at searching and filtering, if they have appropriate indices. Then write queries that use these indices. Log and analyse slow queries to find out why they are slow. Without an index, the search has to filter each item, which gets progressively slower as more items are added.

If the load on your DB server is too high, consider whether the DB can be replicated or sharded. This will allow you to distribute your load over multiple servers. Note that sharding doesn't help if all commonly requested items are on one node.

Whether you use a special database like Elastic Search or just use the features that MySQL already has to offer doesn't matter very much here, if you have a suitable data model. E.g. if full-text search is necessary, then a specialized database might be more appropriate.

Caching by itself will not make all your queries fast. It can merely reduce the load on your DB. If you gather statistics for your queries, you might see that some requests are indeed very common (such as items on a landing page). This might actually be a sufficientl solution for your problem, if your measurements indicate this. But caching doesn't actually speed up each individual query.

Note that you could, in theory, write clever code that filters already-cached data. But at that point, you are reinventing a database. There are likely to be bugs. It is much easier to use the real thing, unless creating a new database engine is the product you are offering.

Answer 3

The point is here:

Nobody is able to give you proper advice without seeing your system.

Of course, you could get general advice like:

• use caching
• use indizes
• use sharding
• use denormalization

etc. All boils down to a) »measure« and b) »better organizing« your data.

Which is all not wrong. But from what you wrote, your system is already tuned, so it doesn't help.

Our database is properly indexed, about this we took great care.

Maybe you have a look at Pilosa. Sounds, that your application is a use case for this.

More background:

• FLOSS WEEKLY 437

• Go Time: Building a distributed index