What is faceted search?
Faceted search is the clustering of search results into categories, allowing users to drill into search results. By showing the number of hits for each facet category, users can easily see how many results match that category.
If you're still a bit confused, this example from CNET explains it all:
The SQL solution for faceted search
Our ("pre-Solr") solution for faceted search was done by adding a lot of redundant columns to our SQL tables and doing a COUNT(...) for each of those columns:
So if a user was searching for real estate properties in the city 'Amsterdam', our facet-query would be something like:
SELECT COUNT(hasGarden), COUNT(yearBuilt1930_1940), COUNT(yearBuilt1941_1950), COUNT(etc...)
WHERE city = 'Amsterdam'
While this solution worked fine for a couple of years, it wasn't very easy for developers to add new facets. And also, performing COUNT's on all matched rows only performs well if you have a limited amount of rows in a table (i.e. less than a million rows).