Databases
StackOverflow2013
Postsdbo
POEfficient implementation of faceted search in relational databases
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEfficient implementation of faceted search in relational databases
    text
    copied!<p>I am trying to implement a <a href="http://en.wikipedia.org/wiki/Faceted_search" rel="nofollow noreferrer">Faceted search</a> or tagging with multiple-tag filtering. In the faceted navigation, only not-empty categories are displayed and the number of items in the category that are also matching already applied criteria is presented in parenthesis. </p> <p><a href="http://forge.mysql.com/wiki/TagSchema#Items_Having_All_of_A_Set_of_Tags" rel="nofollow noreferrer">I can get all items having assigned categories using INNER JOINs</a> and <a href="https://stackoverflow.com/questions/1653280/getting-count-of-related-tags">get number of items in all category using COUNT and GROUP BY</a>, however I'm not sure how it will scale to millions of objects and thousands of tags. Especially the counting. </p> <p>I know that there are some not-relational solutions like <a href="http://lucene.apache.org/solr/" rel="nofollow noreferrer">Lucene + SOLR</a>, but I've found also some closed-source RDBMS-based implementations that are said to be entreprise-strength like <a href="http://facetmap.com" rel="nofollow noreferrer">FacetMap.com</a> or <a href="http://www.endeca.com/" rel="nofollow noreferrer">Endeca</a> software, so there must be an efficient way to perform faceted search in relational databases.</p> <p>Does anybody have experience in faceted search and could give some tips? </p> <p>Cache the counts for each category set? Maybe use some smart incremental technique that will update the counters? </p> <p>Edit:</p> <p>An example of faceted navigation can be found here: <a href="http://orange.sims.berkeley.edu/cgi-bin/flamenco.cgi/famuseum/Flamenco" rel="nofollow noreferrer">Flamenco</a>.</p> <p>Currently I have the standard 3-table scheme (items, tags and items_tags like described here: <a href="http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html#toxi" rel="nofollow noreferrer">http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html#toxi</a> ) plus a table for facets. Each tag has assigned a facet.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload