Refining Search Results [PHP/MySQL]

Question

I'm creating a set of search panes that allow users to tweak their results set after submitting a query. We pull commonly occurring values in certain fields from the results and display them in order of their popularity - you've all seen this sort of thing on eBay. So, if a lot of rows in our results were created in 2009, we'll be able to click "2009" and see only rows created in that year.

What in your opinion is the most efficient way of applying these filters? My working solution was to discard entries from the results that didn't match the extra arguments, like:

while($row = mysql_fetch_assoc($query)) {
    foreach($_GET as $key => $val) {
        if($val !== $row[$key]) {
            continue 2;
        }
    }
    // Output...
}

This method should hopefully only query the database once in effect, as adding filters doesn't change the query - MySQL can cache and reuse one data set. On the downside it makes pagination a bit of a headache.

The obvious alternative would be to build any additional criteria into the initial query, something like:

$sql = "SELECT * FROM tbl MATCH (title, description) AGAINST ('$search_term')";
foreach($_GET as $key => $var) {
    $sql .= " AND ".$key." = ".$var;
}

Are there good reasons to do this instead? Or are there better options altogether? Maybe a temporary table? Any thoughts much appreciated!

Answer 1

I would go ahead and re-query the database, to keep everything together and to reduce pagination nightmares.

Are you doing this via AJAX?

Answer 2

Is using the Memory Storage Engine an option?

If you are doing a large amount of queries (and I mean large), using the memory storage engine can save time. (Though it will be a small amount)