Php – No full-text support; must have effective thesql db search engine; where to find one

MySQLPHPsearchsqlzend-framework

I have asked several questions about Zend and its search functions.
Now after further reading I have noticed that it requires FULL-TEXT indexes in the MySQL fields.
My webhosting provider doesn't allow me to change anything in the my.ini (my.cnf) file, which holds information about minimum length of word to search full-text indexes and more.
So I can't use FULL-TEXT if there is no other way of setting configuration than changing in that file.
Examples of changes are the ft_min_word_len which is by default 4 I think.

I have a table with around 400,000 records, and I need a good search function. It's classifieds btw.

There has to be a way, I just don't know it, so I thought maybe you guys would know.

In the first question I asked regarding Zend I also mentioned I don't have FULLTEXT support, but people suggested Zend anyway.

Can somebody please give me a good explanation of what I should do in my situation?
NOTE: My website is PHP based!

PS: 'LIKE' wont suffice in the searches I need to make. It must be pretty advanced. If you need details about what it should consist of, check my previous Q: Which third party search engine (free) should I use?

Thanks

UPDATE: In two articles, it says Zend "does full-text searches". What do they mean by that? I believe they mean I require full-text indexes!?

Best Answer

Zend_Search in no way requires any full-text searching to be enabled on any database. In fact, Zend_Search is totally independent of any database, as it is a implementation of the Lucene search engine totally in PHP. You should therefore be able to customize it however you wish.

Full text searching is simply the method it uses. So it does do full text searches, but doens't use your database settings (or your database at all)

EDIT
In response to the third comment, Yes, it is in effect a database, but I wouldn't use it as a replacement to a 'true' database as it doesn't have the fields and data integrity support. You can use the UnStored field type so that it only indexes the records, but doesn't store the actual text, so that you can use it in combination with a relational database.

Related Solutions

MySQL search and replace some text in a field

Change table_name and field to match your table name and field in question:

UPDATE table_name SET field = REPLACE(field, 'foo', 'bar') WHERE INSTR(field, 'foo') > 0;

REPLACE (string functions)
INSTR (string functions)

Mysql – Comparison of full text search engine – Lucene, Sphinx, Postgresql, MySQL

Good to see someone's chimed in about Lucene - because I've no idea about that.

Sphinx, on the other hand, I know quite well, so let's see if I can be of some help.

Result relevance ranking is the default. You can set up your own sorting should you wish, and give specific fields higher weightings.
Indexing speed is super-fast, because it talks directly to the database. Any slowness will come from complex SQL queries and un-indexed foreign keys and other such problems. I've never noticed any slowness in searching either.
I'm a Rails guy, so I've no idea how easy it is to implement with Django. There is a Python API that comes with the Sphinx source though.
The search service daemon (searchd) is pretty low on memory usage - and you can set limits on how much memory the indexer process uses too.
Scalability is where my knowledge is more sketchy - but it's easy enough to copy index files to multiple machines and run several searchd daemons. The general impression I get from others though is that it's pretty damn good under high load, so scaling it out across multiple machines isn't something that needs to be dealt with.
There's no support for 'did-you-mean', etc - although these can be done with other tools easily enough. Sphinx does stem words though using dictionaries, so 'driving' and 'drive' (for example) would be considered the same in searches.
Sphinx doesn't allow partial index updates for field data though. The common approach to this is to maintain a delta index with all the recent changes, and re-index this after every change (and those new results appear within a second or two). Because of the small amount of data, this can take a matter of seconds. You will still need to re-index the main dataset regularly though (although how regularly depends on the volatility of your data - every day? every hour?). The fast indexing speeds keep this all pretty painless though.

I've no idea how applicable to your situation this is, but Evan Weaver compared a few of the common Rails search options (Sphinx, Ferret (a port of Lucene for Ruby) and Solr), running some benchmarks. Could be useful, I guess.

I've not plumbed the depths of MySQL's full-text search, but I know it doesn't compete speed-wise nor feature-wise with Sphinx, Lucene or Solr.

Best Answer

Related Solutions

MySQL search and replace some text in a field

Mysql – Comparison of full text search engine – Lucene, Sphinx, Postgresql, MySQL

Related Topic