What are the options besides join to get related information?

Question

SQL

There is a need to display some information on the website, let's say - lists of goods.
Goods are from different suppliers (users), in different regions. Information about them must also be displayed in the product card.
Those. with the products table in queries, there are joins on several tables:

select * from products, users, regions, cities ....

to all this, a bunch of conditions are added to where, which creates problems in creating indexes - it is not possible to achieve good results and find some optimal index.
Make multiple requests for each product of the type

$product = Product::findById(123);
$user = User::findById($product->id)

not an option:
1. There will be too many of them
2. The program code has already been written - it takes a long time to rewrite, there is an ORM that translates the resulting array from the current request into a certain collection of objects ...
There is no cache, it takes a long time to implement.
What other options might there be?