Hi have a MySQL table that I want to do mainly range search on, and I want it fast. All new data is first stored in MySQL, then delta-imported into Solr every minute using DIH. About 20,000 rows are generated each day.
MySQL Schema
The MySQL table contains 6 columns that I want to do range search on (of which 2 are TIMESTAMPs, and there is a pair of geospatial coordinates), 12 columns are TINYINT containing either 0 or 1. There are 30 other columns that do not need to be searched, of which one is the primary key id.
Problem: Is it advisable to store all 48 columns in solr and do the search only in solr? Or should I only have columns I want to search on be stored in solr, and retrieve just the id of the search results from solr, then SELECT the rows that I want to use from the MySQL table?
All help appreciated! :)
In your case I'd suggest storing only attributes you need to search on in Solr (since there are few of them so indexing all the columns would be an unnecessary overhead).
There have been previous questions about this topic:
Solr and MySQL, How to keep an updated index, and, is a DB even needed if it's simple?
Solr / rdbms, where to store additonal data
Solr best practice: All data in Solr or periodically copy to Solr?