So -- this is an oversimplification, and I'm not expert -- a dedicated index is basically a special purpose database designed for nothing more than lightning-fast retrieval of search terms. It's not a general purpose db like mysql, but instead uses [hand wavy CS stuff I'll probably have to learn in grad school] to be zippy at text-based search. So you build your search index dynamically from your db and run searches against it instead of against your primary db, giving you faster response with no db load.

(Everything I'm about to say is about solr, but may well also apply to Sphinx, I dunno.) Solr is also easier for webby people to use rather than needing DBAs to build clever SQL queries. It has REST-like APIs, so you can build the search directly in the URL, eg http://dreamwidth.org/stylesearch?q=fluffy&fq=accessibility:ON&fq=color:blue&fq=inheritance:zesty

That being said we probably don't need it for performance reasons; our set of styles is small as database datasets go.