1

Help me please, remove "Using temporary". I tried many different options, it is impossible to get rid of. Or sorting disappears or "Using temporary" appears. ;(

Category table:

 `product_category_multi` (
      `m_Id` mediumint(7) NOT NULL,
      `prod_Id` smallint(6) unsigned NOT NULL,
      `multi_cat` mediumint(7) unsigned NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Indexes table:

ALTER TABLE `product_category_multi`
  ADD PRIMARY KEY (`m_Id`),
  ADD KEY `multi_cat` (`multi_cat`),
  ADD KEY `Id` (`prod_Id`);

example content table:

m_Id    prod_Id     multi_cat
-------+-----------+---------+  
1       1           5
2       1           1
3       1           6
4       2           5
5       2           1
6       3           5
7       4           5
8       4           6

Product table:

`shop_product` (
  `Id` int(10) unsigned NOT NULL,
  `product_article` varchar(20) NOT NULL,
  `product_article_main` varchar(20) NOT NULL,
  `product_category` int(10) unsigned NOT NULL DEFAULT '0',
  `product_name` varchar(255) NOT NULL,
  `product_price` int(10) unsigned NOT NULL DEFAULT '0',
  `product_active` smallint(1) unsigned NOT NULL DEFAULT '0',
  `product_warehouse_temp` enum('0','1') NOT NULL,
  `product_top` enum('0','1') NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0;

Indexes table:

ALTER TABLE `shop_product`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `product_name` (`product_name`),
  ADD KEY `product_price` (`product_price`),
  ADD KEY `product_article_main` (`product_article_main`);

example content table:

Id  product_article     product_article_main    product_category    product_name    product_price   product_active  product_warehouse_temp  product_top         
--+-------------------+------------------------+------------------+---------------+----------------+--------------+------------------------+------------+
1   qwe                 qwe                     5                   name1           20              1               1                       0
2   asd                 qwe                     5                   name2           30              1               1                       0
3   zxc                 qwe                     5                   name3           50              1               0                       1
4   wer                 sdf                     6                   name4           10              1               1                       0
5   sdf                 sdf                     6                   name5           20              1               1                       0
6   xcv                 sdf                     6                   name6           50              1               1                       0
7   ert                 cvb                     1                   name7           10              1               0                       1
8   cvb                 cvb                     1                   name8           20              1               1                       0

Request:

SELECT
    A.*
FROM
(
    SELECT prod.Id
    FROM shop_product prod
    INNER JOIN (
            SELECT prod_Id
            FROM product_category_multi
            WHERE multi_cat = '5'
        ) AS cat ON cat.prod_Id = prod.Id

    WHERE 
        prod.product_active = '1' 
        AND prod.product_published_start <= 1471376797
        AND prod.product_price = (
            select MIN(temp.product_price)
            from shop_product temp 
            where prod.product_article_main=temp.product_article_main 
        )   
    ORDER BY prod.product_warehouse_temp DESC, prod.product_top DESC, prod.product_review DESC, prod.Id ASC
    LIMIT 0, 20
) B 
INNER JOIN shop_product A USING (Id)

Showing rows 0 - 19 (20 total, Query took 0.0899 seconds.)

EXPLAIN:

id  select_type     table                   type        possible_keys           key                     key_len     ref                             rows    Extra
--+---------------+------------------------+-----------+----------------------+-----------------------+-----------+-------------------------------+--------+-------------
1   PRIMARY         <derived2>              ALL         NULL                    NULL                    NULL        NULL                            20      
1   PRIMARY         A                       eq_ref      PRIMARY                 PRIMARY                 4           B.Id                            1           
2   DERIVED         product_category_multi  ref         multi_cat,Id            multi_cat               3           const                           6869    Using temporary; Using filesort
2   DERIVED         prod                    eq_ref      PRIMARY                 PRIMARY                 4           product_category_multi.prod_Id  1       Using index condition; Using where
4   DEP. SUBQUERY   temp                    ref         product_article_main    product_article_main    62          prod.product_article_main       3

It was this variant, but it does not work "ORDER BY" clause

        SELECT B.*
            FROM(
                SELECT prod_Id
                FROM product_category_multi
                WHERE multi_cat = '5'
            ) cat

            INNER JOIN (
                SELECT 
                    prod.Id
                FROM shop_product prod
                WHERE 
                    prod.product_price = (
                        select MIN(temp.product_price)
                        from shop_product temp 
                        where prod.product_article_main=temp.product_article_main 
                    )
                    AND prod.product_active = '1' 
                    AND prod.product_published_start <= '1471268208'
                ORDER BY 
                    prod.product_warehouse_temp DESC, prod.product_top DESC
            ) prod ON cat.prod_Id = prod.id


        INNER JOIN shop_product B USING (Id)

Showing rows 0 - 24 (2296 total, Query took 0.0046 seconds.)

4

Temp table and filesort are not the evils; they are symptoms that imply inadequate indexes.

Don't use subqueries when JOIN will do the job:

SELECT prod.Id
FROM shop_product prod
INNER JOIN (
        SELECT prod_Id
        FROM product_category_multi
        WHERE multi_cat = '5'
    ) AS cat ON cat.prod_Id = prod.Id

-->

SELECT prod.Id
FROM shop_product prod
INNER JOIN product_category_multi cat
    ON cat.prod_Id = prod.Id
WHERE  multi_cat = '5'

On the other hand, a subquery with an aggregate (MIN(temp.product_price)) is probably better the way you have it.

ORDER BY a DESC, b ASC cannot be optimized. All should be ASC or all should be DESC. In your case, it probably does not matter which way you order id, so change it to DESC. (There may be other issues that prevent any optimization here, but this is a start.) This index might help: INDEX(product_warehouse_temp, product_top, product_review, Id)

If product_category_multi is a many-to-many mapping table, it is inefficient for several reasons. Here is a better way, and it includes the many reasons why it is better.

Use 'composite' indexes where useful: Replace KEY multi_cat(multi_cat) with (multi_cat, prod_id). Replace KEY product_article_main (product_article_main) with (product_article_main, product_price)

In the long run, InnoDB is a better engine than MyISAM, but I won't harp on that at this point. I assume speeding up the query is the real question.

Back to your question... It is unclear how many "temporary" tables this query needs. (EXPLAIN FORMAT=JSON SELECT ... will say.) My suggested indexes may eliminate some of the temp tables, and will probably speed up the query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.