php mysql best way to search for data, single query vs. multiple queries

Question

I have a good knowledge of php and mysql, however I have less practices. Before I have built websites with searches, ajax type of suggestions, etc. However they all lacked some competence.

Now I am going to create a search for my website, which has about 10 tables with different field names. I spent some time figuring out what way to call mysql queries. So I seek for some advice and help.

In case if I select some data that are like some string that was given then I have to create an order of tables, where to search first, which table should be second, so on. That is main reason why I ask this question. Is not it a bad approach to manually run multiple mysql search queries on different tables and try to handle data taken into an array that would be equal to the structure of arrays formed by other tables searches? I mean why should I search for news first, if there is more similarity in article page?

Later I went to an idea of having a single mysql query that fits all the tables, however I could not figure out how to come up with such code that will return an array for the layout and include everything about desired search query.

Please tell me any advanced techniques or practices that you think might help me out. Thanks.

Answer 1

If you have your searchable text distributed among so much tables, you should use some separate search index.

You may use a ready lib to do so, e.g. Apache Solr.

Answer 2

Searching through many tables is not a good idea. Much better to have an index. Index can be a single structured databes table or external engine like sphinx or lucene.

Answer 3

Try for solr search(http://lucene.apache.org/solr/), memcache (http://memcached.org/) or use indexing of mysql. Read the above links and select the most suitable ones

Answer 4

Tip : Create a View with desired data from multiple tables which are mainly used for search