Best practices for filtering data

Question

The way I see it, when building dynamic charts with filters, each time the user requests filtered data I can

1. Execute a new MySQL query, and use MySQL to do the filtering.

   SELECT date,
     SUM(IF( `column` = `condition`, 1, 0)) as count
     ...
   

2. Execute a new MySQL query, and use the server-side language (PHP in my case) to filter.

   function getData(condition)  {
     $resultSet = mysqli_query($link, "SELECT date, column ... ");
   
     $count = 0;
     while ($row = mysqli_fetch_assoc($result_set)) {
         if ($row['column'] == 'condition') {
             $count++;
         }
     }
   }
   

3. Initially execute a single MySQL query, pass all the data to the client, and use Javascript & d3 to do the filtering.

I expect the answer is not black-and-white. For instance, if some filter is barely requested, it may not make sense to make the other 95% of users wait for the relevant data, and thus the filter would necessitate a new data call. But I'm really thinking about edge cases - situations where filters are used regularly, but idiosyncratically. At times like this, is it better to put filtering logic in front-end, back-end, or within my database queries?

Answer 1

Generally, if the filtering can be done on the front end, it should be done there. The advantages are:

• Doesn't matter if your server goes down
• Saves you bandwidth costs
• Saves the user waiting for round trip time

The disadvantages are that it may be slower and more complicated than it would be on the backend. However, dependent on data volume, there's a lot of cases (like your examples) where Javascript is plenty good enough. d3 even has a built-in filter function:

//remove anything that isn't cake
d3.selectAll('whatever')
  .filter(function(d){return d.type != 'cake'})
  .remove()    

If you need more complex filtering, such as basic aggregates, you can use Crossfilter (also from Mike Bostock) or the excellent d3+crossfilter wrapper dc.js.