Alternatives To Make Text Searchable

There are some alternatives available if we don’t want to use built-in options in the databases. We can use third-party search engines or totally database-independent search engines. These are discussed in detail in this lesson.

Third-party search engines

If we need to search text in a way that works the same way regardless of which database brand we use, we need a search engine that runs independently of the SQL database. This section briefly describes two such products, Sphinx Search and Apache Lucene.

Sphinx Search

Sphinx Search is an open-source search engine technology that integrates well with MySQL and PostgreSQL. As of the time this course was written, an unofficial patch existed for using Sphinx Search with the open-source Firebird database. Perhaps in the future, this search engine will support other databases.

Indexing and searching are fast in Sphinx Search, and it supports distributed queries as well. It’s a good choice for high-scale searching applications that have data that update infrequently.

We can use Sphinx Search to index data stored in a MySQL database. By modifying a few fields in a configuration file sphinx.conf, we can specify the database. We must also write an SQL query to fetch the data for building the index. The first column in this query is the integer primary key. We may declare some columns as attributes for restricting or sorting results. The remaining columns are those to be full-text indexed. Finally, another SQL query fetches a full row from the database, given a primary key value coded as $id.