I have data for a certain domain distributed between a microservice and a legacy database. I have a search which spans fields on both legacy and microservice database. Previously (before microservice split), it was done with 1 sql query. Now I need a REST call and a query to legacy database to serve this search functionality. We are talking about a few million rows here. How can I model this best? Due to volume of data, REST call returns paginated results usually as well. Naive approach to fire a SQL call and combine and merge results with the REST response is too slow and not really practical.
A search feature can be modelled as a separate service with separate responsibility from the two services you mention. So, the approach here could be to create a new service ('search') and have it store a copy of the data from both services in a form which is easy to index and search, possibly also denormalized in order to quickly give results in the desired format.
So, for example you could have the legacy SQL database using e.g. mySql, the other microservice using e.g. MongoDB, and the new search service using elasticsearch with data from both already pasted together (denormalized) for more convenient access. of course the details will depend upon the kind of searches you need to perform.
Data from the two services would best be transferred asynchronously to the search index via an event bus such as Kafka or Hermes in order to increase throughput and reduce coupling between the services. A change in any of the two services would send an event informing the search service to also update its data.
Of course there is the cost of an additional delay between changes in the services and in the search service, but since microservices are usually used in systems which are distributed, some delays and temporary inconsistencies are unavoidable anyway. Having an additional service and using extra storage for a copy of data which is already in the other two services is also a typical cost of having a highly distributed and scalable system using microservices.