Full text indexed search
23 July 2012
Today we launched a new website for the Erasmus Bookshop and Café network in Romania. While the bookshop itself stocks close to 4,000 items they also have access to a further 400,000 items from a German supplier.
As you may be aware, with a standard SQL search engine, the more items in the database the longer it takes for the results to display. An SQL search is normally alright for up to 20-30,000 items, but after that begins to slow down noticeably.
The solution to this bottleneck is to instead use an 'indexed' search. This means that the database is used to generate a 'search index' which the website can then query in real time rather than having the scan the entire database.
Search indexes are specially designed for full text searching, and when customised for a specific website or database can be extremely fast. Google and other search engines all use this technique (plus a lot of hardware).
For the Erasmus project we opted for the Sphinx Open Source Search Server which runs on Linux and is compatible with our database.
The result is that we can search the more than 400,000 books and other items in the database in a fraction of a second and return the results nicely ordered by relevance.
The website features both a 'simple' search at the top of every page as well as an advanced search option where you can specify words to look for in the Title, Author, Publisher and other fields. You can check it out using the link below.