Hi,
I am interested to find out what is the best/fastest (most efficient) way to query solr in a solr/mysql/app set up. I have a mysql DB that has one large main table and several smaller tables in a relational schema. I am also building an app which uses the hierarchy and builds menus based upon the data in the related tables.
I started by doing this only in mysql, but quickly found (with gigs of data) that mysql can be quite slow calculating counts on this related data when using joins with the main table etc (even when using mysql indexes). At the moment the approach i am taking is to index my main table with solr and keep the smaller related tables in mysql. For each menu item i will query solr for the count at runtime, which feels like it will be slow..
Is it faster/better to:
1.) Keep the related tables in mysql, also set up facets for each row in the related tables. Link them together somehow when i query the maintable? This sounds like the fastest option, but might be tricky (will have to match 2 different arrays) in my app.
2) Keep the related tables in mysql and call/count the data in the main indexed table for each of the related items at runtime. e.g. for a menu of brands I will need to get a count of each brand, requiring me to send each menu item as a query to solr (to get the counts). I realise each query is quite quick, but there could be a few hundred or thousand brands.
3) Just put all the data into solr and use facets? - but how do I identify each facet and determine the related information in mysql tables show for each facet? Each entry in the related mysql tables has a title, description, formatted url, meta data, should the related info be stored in solr as well? in a different index? in this case, should i get rid of mysql alltogether?
Any ideas on the best (practice) option will be much appreciated, or also any suggestions that i havent thought of would be great.
Cheers Ke