How can you build a search engine for a website built in PHP/MySQL?
11th February 2012
My answer to How can you build a search engine for a website built in PHP/MySQL? on Quora
There are a bunch of options.
The easiest to implement is to build search on top of MySQL LIKE queries—performance will be pretty terrible (since every search will require a full table scan) but provided your tables only have a few thousand records on them and your site doesn’t have to cope with more than a dozen or so hits a second it should work fine.
Next easiest: use MySQL’s built-in full text indexing feature. It’s not particularly good, and it requires you to use MyISAM tables (InnoDB is much more reliable, but doesn’t support full text indexing)—but it will do the job. You could always keep your main site data in InnoDB and denormalise in to a MyISAM table just for search—or you could use the trick Flickr used to use, which is to set up MySQL replication and run MyISAM on one of the slaves purely to support fulltext search.
Past that, you’re looking at adding another component to the stack. Sphinx can integrate directly with MySQL and lets you run SQL-style queries against a proper full text index. Personally I’m a big fan of Solr, which runs as a separate (Java) server and requires you to index documents over HTTP. The great thing about Solr is that you can talk to it from any language that has an HTTP client library.
The last option is to go for a hosted solution. Google Custom Search is free, but not particularly flexible. IndexTank was a good option here but they were acquired by LinkedIn and are shutting down the hosted service—they’ve since open sourced their software and other companies such as http://www.searchify.com/ are starting to offer it as a hosted solution.
More recent articles
- Things I've learned about building CLI tools in Python - 30th September 2023
- Talking Large Language Models with Rooftop Ruby - 29th September 2023
- Weeknotes: Embeddings, more embeddings and Datasette Cloud - 17th September 2023
- Build an image search engine with llm-clip, chat with models with llm chat - 12th September 2023
- LLM now provides tools for working with embeddings - 4th September 2023
- Datasette 1.0a4 and 1.0a5, plus weeknotes - 30th August 2023
- Making Large Language Models work for you - 27th August 2023
- Datasette Cloud, Datasette 1.0a3, llm-mlc and more - 16th August 2023
- How I make annotated presentations - 6th August 2023
- Weeknotes: Plugins for LLM, sqlite-utils and Datasette - 5th August 2023