delay advice - Huge resultset I have a mysql multi select query that performs inner left joins. These queries UNION with two other tables. The resultset is huge (250k+ rows). My client wishes to expose this resultset to a webpage. The infinite scroll methodology used on the webpage calls the above query with an offset limit - 50 rows per call. The time taken to retrieve the above dataset is 7+secs.
Can anyone offer any advice as to how this delay could be reduced?  

SELECT 'address_messages_unique' as db,`block_height`,`block_hash`, `block_timestamp`, `transaction_hash`, `data_hash`, `address_messages_unique`.`id`, `tags`, CONVERT(UNHEX(`address_messages_unique`.`data`) USING utf8mb4) as utf8_data, `mftm-Mess`.`MID`, `TTS`.`SSML`, `TTS`.`VoiceId`
FROM `messages_from_the_mines`.`address_messages_unique`
left join `mftm-Mess`
on `mftm-Mess`.`ID` = `address_messages_unique`.`id`
left join `TTS`
on `TTS`.`MID` = `mftm-Mess`.`MID`
UNION
SELECT 'coinbase_messages_unique' as db,`block_height`,`block_hash`, `block_timestamp`, `transaction_hash`, `data_hash`, `coinbase_messages_unique`.`id`, `tags`,CONVERT(UNHEX(`coinbase_messages_unique`.`data`) USING utf8mb4) as utf8_data, `mftm-Mess`.`MID`, `TTS`.`SSML`, `TTS`.`VoiceId`
FROM `messages_from_the_mines`.`coinbase_messages_unique`
left join `mftm-Mess`
on `mftm-Mess`.`ID` = `coinbase_messages_unique`.`id`
left join `TTS`
on `TTS`.`MID` = `mftm-Mess`.`MID`
UNION
SELECT 'op_return_address_messages_unique' as db, `block_height`, `block_hash`, `block_timestamp`, `transaction_hash`, `data_hash`, `op_return_address_messages_unique`.`id`, `tags`,CONVERT(UNHEX(`op_return_address_messages_unique`.`data`) USING utf8mb4) as utf8_data, `mftm-Mess`.`MID`, `TTS`.`SSML`, `TTS`.`VoiceId`
FROM `messages_from_the_mines`.`op_return_address_messages_unique`
left join `mftm-Mess`
on `mftm-Mess`.`ID` = `op_return_address_messages_unique`.`id`
left join `TTS`
on `TTS`.`MID` = `mftm-Mess`.`MID`
order by `block_height`
limit 0, 50;


The resultset (which essentailly is the output of the entire db needs to viewed page by page
Thanks  Akina - your message helped me get on the right track and sort this out:


  Copy this result to (temporary) table with additional autoincrement field (with the name rownum, for example, autoincrement field will (must) be PRIMARY KEY, so it will be filled with values automatically), then expose the data from this table and select first/next chunks by this field values (WHERE rownum BETWEEN 50*@chunknum-49 AND 50*@chunknum).


I ended up creating a new table using the previous resultset with the rownum autoincrement that you suggested