Exploring search options for PHP

Question

I have innoDB table using numerous foreign keys, but we just want to look up some basic info out of it.

I've done some research but still lost.

1. How can I tell if my host has Sphinx installed already? I don't see it as an option for table storage method (i.e. innodb, myisam).

2. Zend_Search_Lucene, responsive enough for AJAX functionality of millions of records?

3. Mirror my innoDB with a myisam? Make every innodb transaction end with a write to the myisam, then use 1:1 lookups? How would I do this automagically? This should make MyISAM ACID-compliant and free(er) from corruption no?

4. PostgreSQL fulltext queries don't even look like SQL to me wtf, I don't have time to learn a new SQL syntax I need noob options

5. ????????????????????

This is high volume site on a decently-equipped VPS

Thanks very much for any ideas.

Answer 1

Your question is very vague on what you're actually wanting to accomplish here but I can tell you to stay away from Zend_Search_Lucene with record counts that high. In my experience (and many others, including Zend Certified Engineers) ZSL's performance on large record-sets is poor at best. Use a tool like Apache Lucene instead if you go that route.

Answer 2

Sphinx is very good choice. Very scalable, built-in clustering and sharding.