Selecting big data from a database with complex queries?

Question

PHP

There is a php/mysql project. There are moments in the system when you need to make quite complex and large queries, with a large selection.
Queries can contain more than 5 joins (sometimes from the same table, which works as a meta table with keys and values), as well as UNION selects and so on.
When you need to make a selection from 20k-25k records, it takes about 15 seconds, which is not normal in principle.
What optimization techniques can be used here?
What options are there?
Maybe: Caching? Pagination (adds problems)?

Answer 1

EXPLAIN query, indexes. Sometimes it helps to replace JOIN with a subquery without data dependencies (if the database itself can cache the result). Also, if you have problems with joins, views save.

Answer 2

there are also wonderful operators in the select: limit and offset. Sometimes they help.

select * from (
   select * from table1
   union
   select * from table2
) limit 100 offset 200

Answer 3

To begin with, you need to understand that no living user will ever read 20,000 entries on one page.
after that you can continue the optimization