How to optimize a query to MySQL when selecting from many tables?

Question

MySQL

I have tables - supplier prices (now less than 10, there will be dozens) - named after the supplier (ID, product ID, description, price, update date) - each has several thousand products and products (product ID, name).
There is a query with which I get a table with products that are in stock and the name of the supplier who has the cheapest product:
product | supplier | description of goods from this supplier | price
SELECT * FROM (SELECT * FROM (
for the first supplier
SELECT article_id, "supplier x" AS supplier, description, price FROM `supplier x`, `articles` WHERE (`supplier x`.`article_id` = `articles`.` id` AND DATE = (SELECT MAX( DATE ) FROM `vendor x`) AND price IS NOT NULL)
for each subsequent supplier
UNION ALL SELECT article_id, "supplier x+1" AS supplier, description, price FROM `shop`.`supplier x+1`, `articles` WHERE (`supplier x+1`.`article_id` = `articles`.` id` AND DATE = ( SELECT MAX( DATE ) FROM `supplier x+1` ) AND price IS NOT NULL)
then
) a ORDER BY article_id, price ASC ) b GROUP BY article_id';
Due to the fact that the number of suppliers will increase, as well as products that are not currently available, I need to optimize this query.
As far as I understand, the first thing to do is to create tables that will contain the actual prices of suppliers (so as not to select the maximum date) and work with them.
The second step that I see is to create a final table, and when changing the price of a particular supplier, change only those rows in it that contain goods that this supplier has.
Am I thinking right? How else can you optimize the query?