PHP中的Mysql表索引

Question

I have a MYsql database containing names of books, authors and other details.

Which indexing engine can search them?

I wanted to use Sphider.

Will it work for MySQL databases?

Answer 1

Solution 1: Use MyISAM table engine for your tables that contain the records you want to search. Then you can create indexes on columns you want to search and then apply full text search functions. http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

Note: When using MyISAM you will not have transactional capabilities, and tables might get corrupted (recovery is difficult). Also, as of MySQL 5.6.4 InnoDB has full text support (though it's a new feature so it might be buggy and benchmarks are still not ample).

Solution 2: Use Sphinx search server. It is super fast and distributed search system. Sphinx will pull fields you want to index from your MySQL database and index them. Then you can either use Sphinx SQL to fetch the document IDs matching your query or use Sphinx API (provided for well known languages) to get document IDs matching. http://sphinxsearch.com/

Sphinx is super fast when it comes to indexing and query serving. It's worth taking a look at. I would recommend it.