databaseMySQLsearch
I'm building an Online Food Ordering System with PHP and MYSQL, One of the feature of the application is to allow users to search for the restaurants by typing the area name.
I would like to know using PHP for search will be more efficient or using Stored Procedure or using any third party tool.
Efficient in the sense when it is to be searched among large set of data which one will be the fastest?
As a pre-processing step turn your list into a trie.
a trie, also called digital tree or prefix tree, is an ordered tree data structure that is used to store a dynamic set or associative array where the keys are usually strings. Unlike a binary search tree, no node in the tree stores the key associated with that node; instead, its position in the tree defines the key with which it is associated. All the descendants of a node have a common prefix of the string associated with that node, and the root is associated with the empty string. Values are normally not associated with every node, only with leaves and some inner nodes that correspond to keys of interest...
The term trie comes from re-trie-val...
Why not let the text search engine like lucene or sphinx do all the work? I use Lucene and have found it to be very good at searching multiple tables and fields for whatever you set as a target.
You don't say what your web front end is written in but both of these tools can be used with most languages.
You will be adding a lot of code to maintain in the triggers that watch for created/modified/deleted in all the tables. Wouldn't it be easier to add the option for admins to mark a column as "to be searched". Then write code to add this column to indexed fields?
I see that Sphinx has "offline index construction and incremental on-the-fly index updates" which would seem to allow you to add a field, rebuild the index offline and then bring the new index up.
Best Answer
Most likely this should be done with SQL. This would be simpler and have better performance.
But would depend on a few details. Especially how exact or complicated you want it to be able to find the information the user typed in (maybe a list of available areas would be a better ui option here)
You could do a simple search like:
This would of course fail for any typo the user makes and maybe return several records and you would have to offer the user further options to select one of those.
Assuming at some point you found a single record, it should be simple to find restaurants by area_id.