MySQL’s full-text search capability* is fast for smaller datasets but performs badly when the data size grows. With millions of records and gigabytes of indexed text, query times can vary from a second to more than 10 minutes, which is unacceptable for a high performance web application. Although it’s possible to scale MySQL’s full-text searches by distributing the data in many locations, this requires you to perform searches in parallel and merge the results in your application. Sphinx works significantly faster than MySQL’s built-in full-text indexes. For instance, it can search over 1 GB of text within 10 to 100 milliseconds—and that scales linearly up to 10–100 GB per CPU. Sphinx also has the following advantages:

• It can index data stored with InnoDB and other engines, not just MyISAM.

• It can create indexes on data combined from many source tables, instead of being limited to columns in a single table.

• It can dynamically combine search results from multiple indexes.

• In addition to indexing textual columns, its indexes can contain an unlimited number of numeric attributes, which are analogous to “extra columns.” Sphinx attributes can be integers, floating-point numbers, and Unix timestamps.

• It can optimize full-text searches with additional conditions on attributes.

• Its phrase-based ranking algorithm helps it return more relevant results. For instance, if you search a table of song lyrics for “I love you, dear,” a song that contains that exact phrase will turn up at the top, before songs that just contain “love” or “dear” many times.

• It makes scaling out much easier.

Source of Information : OReIlly High Performance MySQL Second Edition


Subscribe to Developer Techno ?
Enter your email address:

Delivered by FeedBurner