Database denormalization, structure change or something else to add?

Question

MySQL

Good afternoon, there is such a database structure.
I need to select all collections according to the given parameters, the collection needs two pictures. Depending on the parameters, the pictures can be both of the product (plitka_product.img) and from the picture where they are located. (interior_goods.img)
Here is the structure of it all.
I ask you to give any tips on the solution, otherwise requests for 4-8 seconds are trash.

mysqli_query($this->db, 'SET @row_number = 0;');
mysqli_query($this->db, 'SET @collection = 0;');

SELECT t2 .* FROM(SELECT t1 .*,
        @row_number:=
        CASE WHEN @collection = t1 . collection_id
        THEN @row_number + 1
        ELSE 1 END AS num, @collection:=t1 . collection_id AS collection
        FROM(SELECT
          linkVisual,factory_name,factory_id,collection_name,collection_id, 1 as type
        FROM
                (SELECT
                  plitka_tovar . id,
                  plitka_tovar . Poverhnosty,
                  plitka_tovar . Material,
                  plitka_tovar . Tekstura,
                  plitka_tovar . tip,
                  plitka_tovar . Vid_izdeliya,
                  plitka_tovar . `S_Facetom(Kabanchik)`,
                  plitka_tovar . Tehnicheskiy_keramogranit,
                    interior_goods . imgnew AS linkVisual,
                  plitka_fabrika . Nazvanie AS factory_name,
                  plitka_kollekciya . Nazvanie AS collection_name,
                    plitka_fabrika . id AS factory_id,
                    plitka_kollekciya . id AS collection_id,
                    plitka_kollekciya . collection_link
                  FROM
                    plitka_tovar,
                    _article,
                    interior_point,
                    interior_goods,
                    plitka_kollekciya,
                    plitka_fabrika
                  WHERE
                    _article . id_part = '18'
                      AND _article . id_product = plitka_tovar . id
                      AND interior_point . article = _article . id
                      AND interior_goods . id = interior_point . id_goods
                      AND plitka_fabrika . id = plitka_tovar . Fabrika
                      AND plitka_kollekciya . id = plitka_tovar . kollekciya)
    $table
                    $where
        UNION
                SELECT t1 . imgnew as linkVisual,t2 . Nazvanie as factory_name,t2 . id as factory_id, t3 . Nazvanie as collection_name, t3 . id as collection_id, 2 as type
          FROM plitka_tovar as t1, plitka_fabrika as t2, plitka_kollekciya as t3 $table
          WHERE t1 . Fabrika = t2 . id AND t3 . id = t1 . Kollekciya $whereTov
        GROUP BY concat(collection_id, linkVisual)

                ORDER by
                  collection_id,type
                  ) as t1) as t2
        WHERE t2 . num <= 2
        ORDER BY  t2 . collection_name,t2 . collection

mysqli_query($this->db, 'SET @row_number = 0;');
mysqli_query($this->db, 'SET @collection = 0;');
I use these lines to select two pictures, for one collection
$where $table - these are those additional tables, if there is a request for them.
My question is how to simplify it, because in this mode my request takes a very long time, too long.
Indexes are made for tables.
The tables are not linked using a key, well, that is, it is not directly spelled out, is this an omission in terms of speed or not?
I am using the database cache.
I would be grateful for any tips and information.

Answer 1

Trash is not a table structure, which is the simplest externally, but 4 levels of nesting SELECT'a. This is a shooting article. Deal with queries first, and only then with data normalization.