1

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! :)

Nyxynyxx
  • 196
  • 3
  • 9

1 Answers1

0

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?

Community
  • 1
  • 1
Marko Bonaci
  • 5,622
  • 2
  • 34
  • 55
  • Does doing the 2nd search in MySQL slow the entire search down alot ? – Nyxynyxx Sep 24 '12 at 23:48
  • 1
    Usually you'd go to Solr to get the list of results (Solr needs to contain all the information needed). When the user clicks a specific product, you can go to the database by id to show the details of that specific product itself. – javanna Sep 25 '12 at 08:39
  • You wouldn't do a search in MySQL, you'd simply fire sql select statement to get specific documents (records). To do that you would use document's primary key (which is indexed by default) so it would be very fast and wouldn't hinder your overall performance. – Marko Bonaci Sep 30 '12 at 13:32