I am looking to join multiple big tables in the OLAP layer to power the UI. Since the tables are really large, response for each join query takes too long. I want to get results in less than 3 seconds. But the catch is I don't want the entire joined data at once because I am only displaying a small subset of the result in the UI at any particular point. Only user interaction would require me to show the next subset of the result.
I am looking for a strategy to create a system where I can perform the same join queries, but initially only a small subset is joined and used for powering the UI. Meanwhile, the rest of the smaller subsets of data is joined in the background and that gets pulled into the UI when required. Is this the right way to approach this problem, where I have to perform really big joins? If so, how can I design such a system?
You can use a WITH HOLD cursor:
START TRANSACTION;
DECLARE c CURSOR WITH HOLD FOR SELECT /* big query */;
FETCH 50 FROM c;
COMMIT;
The COMMIT will take a long time, as it materializes the whole result set, but the FETCH 50 can be reasonably fast (or not, depending on the query).
You can then continue fetching from the cursor. Don't forget to close the cursor when you are done:
CLOSE c;