yes and no my idea was even more abstracted - I try to elaborate with your example..
I think for a text search, something like this would be benefitial..
apple starts with Ap and contains "pp" - both are specific, recognizable parts of the term, that can narrow down the search result
an Index, that can deliver rows, where one of those fits, would help..
so "Ap" is found in rows 1500-2200 and 3215-3400 etc...
pp can be found found in rows x - y
even if the row list looks like this
with the hint from above
=> "create the final sql-string in a loop that ads a condition for each what-ever-you-search and run at the end only one query in (preferably only one) table at a time"
you will save a lot of memory..
the bigger your table, the more you save..
each time a new term is entered in the database, a specially made function, analysis the term and slpits it in to "searchable" parts like "Ap" or "pp"
than you add the row number of the new term to the index-table where it fits..
Either you define those "parts" or you get some text-analyzer that can do that..
look at phpclasses.org - Im sure you will find anything that suits this job..
Alltogether it may look too complexe for the task - but imagine millions of rows and a memmory error on each search..
if that happens, a complexe but working solution is preferable..
However - those hints are just methods to reduce memory usage and other problems you "might" encounter..
as I said, Im not even sure, at what point you will reach such problems
but IF they come, try those methods..
Im trying to put all in a relation:
if your search is just for one website, you will have some thousand rows if it's a big site - you will NEVER have any problems with fulltext on that size
only if you have scanned millions of websites, or hundreds of books, etc..
for example I made for a client a full text search on about 20 different versions of the bible in 10 different languages..
those are hundredthousands of verses (1 row in db for each verse) - I didnt use an index as described - and I have no issues with searching..
so if your project is NOT much bigger, skip the above, ignore the rants about php and code your fulltext search - it will work..
I used as pressumption, that you mean a search engine in the size of google or so - there you might find memory trouble and need those tricks
you said "on a large database would be a massive memory drain"
in my understanding, that issue is irrelevant below let's say 2million rows.. (estimation-alert)
<irony> ok ok WP can have such memory problems already with 8 plugins and no content at all - but since we dont do it the same way than the WP guys... </irony>
if the WP fans say php is not suited for big jobs because WP has those issues, it's just some wannabe-smart-ppl who have no clue what they talk about..