It sounds like you want to give users an overview of a search result before they begin filtering. You might want to think about the problem you're trying to solve before focusing on technical challenge of presenting millions of rows.
User Goals
What are your users' goals? What tasks are they performing? Is searching, then sorting and filtering a large set of data the ultimate goal or a means to an end?
- If sorting/filtering is the goal: then you're on the right track and you just need to look into smart ways to asynchronously load the data and manage the search filters, which is definitely a technical question that involves front-end and server work.
- If the goal is finding a single data point or small data set: Invest time finding ways to providing better / more relevant results. What signals can you gather from the user to anticipate their needs a little better thereby predict what part of the data set they need? Even reducing the result set by a fraction is a huge improvement.
Bottom line
If displaying a data set with millions of rows to a user has no immediate utility (it probably doesn't), then don't present it at all.
Do users need to get the gist of their search they just performed? Then summarize the results. Break the millions of results into meaningful chunks and use design cues to reduce the cognitive load:
- Build a summary UI that gives users a good way to dive into the details of the data set.
- Add meaning through charts, stats, highlights.
Going for it
That said, if you're sure you'll need to present several thousand results to the user, then here's what I'd do. Load the data asynchronously using a front-end framework to keep the UI responsive while data is streaming in.
- Load a handful of masters initially, enough to fill a couple pages.
- Then load the details you expect will be viewed first.
- When users are ready for more results, use pagination or infinite scroll. As users scroll down, the browser can asynchronously load more masters and lazy load their details. The combination of infinite scroll and faceted search can be great, but it's more technically demanding than pagination.
- If you have multiple details per master, you can use the same pagination or infinite scroll to show additional details.
This requires skill
- The more font-end and server skills involved, the better. This isn't going to be an off the shelf solution, as the app needs to balance UI responsiveness with network performance with memory overhead.
- For transit: JSON will likely cut it, but a more sparse purpose-built data format will reduce overhead across the wire.
An example
Vimeo's faceted search. In this example the "detail" is a video and it's therefore loaded in a new page, but the concept holds true - there's no technical reason they couldn't show the video inline on the same page as the search results.
- Results are pulled in asynchronously as you manipulate the search filters.
- The first few results are shown, then the rest are paginated.
- There's no reason you can't switch from pagination to an infinite scroll pattern. But make sure you have the required skills on board or it won't pan out.
- Bonus: Note how even though the data is being loaded async, the URL in the address bar updates in real time as you change the search facets. It makes bookmarking, saving, and sharing searches a snap. This is a big win; one drawback of early async implementations was no reflection of query or UI state in the URL.
