Is it better to do a large query and filter with jquery, or do more smaller queries?

Question

I'm writing my first website using php/mysql, and jquery. For the next part, the user will apply filters and sorts to find specific items (like an advanced search, kind of) and there will also be a search box. I want the data to reflect the change as soon as any checkbox filter is changed (currently have a test ajax call with no database query to do this that works well).

Would it be better to re-form the query string and re query the data each time a filter changes, or to make one large query and filter the results depending on the filters?

For the time being, the number of records will be low, but it's possible to grow into the thousands.

Answer 1

I would say build the query solely based on the data the user has selected, simply because when the data does become large and unwieldy, it would be silly to send that all to the client. It would be detrimental to performance in two main ways, if you think about it:

1. Large data download to the client.
2. JavaScript will need to process (sort/filter) the results and display what the user has asked for.

This is a no brainer, in my books. You will end up having to re-work your solution to scale with the size of your database, which is something you don't want.

The server is good at handling queries and sorting and filtering through truckloads of data. That's not really something you should be doing on the client side if you have a choice.

Answer 2

In this case I would suggest ajax calls to get the filtered information. You also may want to limit the number of options shown. Instead of having to render thousands of options (which could be time consuming) you could limit your query to get no more than 25, 50 or 100 options. That saves time (less information to retrieve, less work to do server side, faster data transfer and faster client side operations due to smaller amount. And your users still can use the filtering to get the information they need.

If the number of records is likely to remain low (tens of records instead of thousands) it could be faster to implement client side filtering without using ajax. But then you need to find a way to filter the data in javascript, that could have implications of its own.