Which method should I go with; Indexing MySQL db with SOLR

Question

I have a classifieds website, with approx 30 categories of classifieds.

I am on the stage where I have to build MySQL tables and index them with SOLR. Each row in a table has around 15 fields...

I am looking for performance!

I wonder which of these two methods works best:

1- Have one MySQL table for each category, meaning 30 tables, and then have multiple indexes in SOLR ( This would mean that if the user only wants to search in one specific category, then that table/index is searched, thus gaining performance (I think). However, if the user searches ALL categories at once, then all tables/indexes would have to be searched. )

2- Have one and only one MySQL table, and only one index in SOLR.

Thanks

Answer 1

Assuming that all of the different types of classifieds have the same structure, I would do the following:

Store the text in a single table, along with another field for category (and other fields for whatever other information is associated with a category).

In Solr, build an index that has a text field, a category field, and a PK field. The text and category fields would be indexed but not stored, and the PK field (storing the primary key corresponding to your MySQL table) would be stored but not indexed.

Allow the user to do two kinds of searches: one with just text, and one with text and category. For the latter, the category should be an exact match. The Solr search will return a list of PKs which will allow you to then retrieve documents from MySQL.

You will not see much of a performance improvement by splitting your index up into 30 indices, because Solr/Lucene is already very efficient at finding data via its inverted indices. Specifying the category name is sufficient.