We are developing a search function for space rental products using Elasticsearch and MySQL.

[Search form]

• From DateTime
• To DateTime
• Min Price
• Max Price

The search form is the same as above, and there is no problem with Fulltext-search in ES.
The problem is that when you search for a price, you need to calculate the amount to be paid within the search time in real time, search for a price range with the calculated price, and complete paging.

ES searches Fulltext-search
MySQL calculates prices in real time and filters the calculated prices according to Min Max Price
I need to page the records that are searched in ES and MySQL
There is a problem that the number of records that are searched on both systems do not match, so I am asking for help on a solution.

To solve this issue, I thought about calculating and filtering using scripts in ES without searching in both ES and MySQL, but I think the paging issue can be solved completely, but I think the performance will be very poor, so I ask for your opinions from experts.

I thought about two things to solve this issue.

1. I also thought about calculating and filtering using scripts in ES without searching in both ES and MySQL, but the paging issue will be completely resolved, but I think the performance will be very low.

2. We also considered importing more than two to three times the record count from both ES and MySQL, and merging the search results from the Spring Boot project or Spark. However, certain pages may have issues that return only one or two.

Please recommend a good way to solve the problem and improve the search performance.