Best solution for custom live search task

Question

I'm going to add simple live search to website (tips while entering text in input box).

Main task:

39k plain text lines for search into (~500 length of each line, 4Mb total size)
1k online users can simultaneously typing something in inputbox
In some cases 2k-3k resuts can match user request

I'm worried about the following questions:

Database VS textfile?
Are there any general rules or best practices related to my task aimed for decreasing db/server memory load? (caching/indexing/etc)
Do Sphinx/Solr are appropriate for such task?

Any links/advice will be extremely helpful.

Thanks

P.S. May be this is the best solution? PHP to search within txt file and echo the whole line

Take a look at this I posted a while back http://stackoverflow.com/a/13830962/1880925 — ROY Finley, Dec 18 '12 at 14:10

score 2 · Answer 1 · answered Dec 18 '12 at 14:04

Put your data in a database (SQLite should do just fine, but you can also use a more heavy-duty RDBMS like MySQL or Postgres), and put an index on the column or columns that will be searched.

Only do the absolute minimum, which means that you should not use a framework, an ORM, etc. They will just slow down your code.

Create a PHP file, grab the search text and do a SELECT query using a native PHP driver, such as SQLite, MySQLi, PDO or similar.

Also, think about how the search box will work. You can prevent many requests if you e.g. put a minimum character limit (it does not make sense to search only for one or two characters), put a short delay between sending requests (so that you do not send requests that are never used), and so on.

Whether or not to use an extension such as Solr depends on your circumstances. If you have a lot of data, and a lot of requests, then maybe you should look into it. But if the problem can be solved using a simple solution then you should probably try it out before making it more complicated.

thanks! also interesting idea about delaying requests. got it — Vladimir Kishlaly, Dec 18 '12 at 14:23

score 1 · Answer 2 · answered Dec 18 '12 at 14:10

In the past, i have used Zend search Lucene with great success.

It is a general purpose text search engine written entirely in PHP 5. It manages the indexing of your sources and is quite fast (in my experience). It supports many query types, search fields, search ranking.

score 1 · Accepted Answer · answered Dec 18 '12 at 14:11

I have implemented 'live search' many times, always using AJAX with querying the database (MySQL) and haven't had/observed any speed or large load issues yet.

Anyway I saw an implementations using Solr but cannot suggest whether it was quicker or consumed less resources.

It completely depends on the HW the server will run on, IMO. As I wrote somewhere, I had seen a server with very slow filesystem so implementing live search while reading and parsing from txt files (or using Solr) could be slower than when querying the database. On the other hand You can host on poor shared webhosting with slow DB connection (that gets even slower with more concurrent connections) so this won't be the best solution.

My suggestion: use MySQL with AJAX (look at this jquery plugin or this article), set proper INDEXes on the searched columns and if this is found slow You still can move to a txt file.

Best solution for custom live search task

3 Answers3