[MySQL] 结合业务特性巧用临时表

SELECT  p.products_id FROM products AS p
JOIN products_to_categories AS pc USING(products_id)
JOIN categories AS c USING(categories_id)
JOIN products_realtime_quantity AS prq ON prq.sku_or_poa = p.products_model
WHERE products_status =1 AND categories_status =1   AND prq.msg != 'Temporary out stock.'
ORDER BY p.products_date_added DESC LIMIT 10

+----+-------------+-------+--------+------------------------------+-------------------+---------+--------------------------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys        | key         | key_len | ref              | rows   | Extra                    |
+----+-------------+-------+--------+------------------------------+-------------------+---------+--------------------------------+--------+----------------------------------------------+
|  1 | SIMPLE    | pc  | index  | PRIMARY,categories_id    | PRIMARY       | 8     | NULL               | 1009510 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE    | p   | eq_ref | PRIMARY,products_model     | PRIMARY       | 4     | banggood_work.pc.products_id   |    1 | Using where                  |
|  1 | SIMPLE    | c   | eq_ref | PRIMARY            | PRIMARY       | 4     | banggood_work.pc.categories_id |    1 | Using where                  |
|  1 | SIMPLE    | prq   | ref  | ix_prg_sku_or_poa,ix_prq_msg | ix_prg_sku_or_poa | 152   | banggood_work.p.products_model |    1 | Using where                  |
+----+-------------+-------+--------+------------------------------+-------------------+---------+--------------------------------+--------+----------------------------------------------+

EXPLAIN
  -> SELECT  p.products_id FROM products AS p   FORCE INDEX(products_date_added)
  -> JOIN products_to_categories AS pc USING(products_id)
  -> JOIN categories AS c USING(categories_id)
  -> JOIN products_realtime_quantity AS prq ON prq.sku_or_poa = p.products_model
  -> WHERE products_status =1 AND categories_status =1   AND prq.msg != 'Temporary out stock.'
  -> ORDER BY p.products_date_added DESC LIMIT 10
  -> ;
+----+-------------+-------+--------+------------------------------+---------------------+---------+--------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys        | key         | key_len | ref              | rows | Extra     |
+----+-------------+-------+--------+------------------------------+---------------------+---------+--------------------------------+------+-------------+
|  1 | SIMPLE    | p   | index  | NULL             | products_date_added | 8     | NULL               |  1 | Using where |
|  1 | SIMPLE    | prq   | ref  | ix_prg_sku_or_poa,ix_prq_msg | ix_prg_sku_or_poa   | 152   | banggood_work.p.products_model |  1 | Using where |
|  1 | SIMPLE    | pc  | ref  | PRIMARY,categories_id    | PRIMARY       | 4     | banggood_work.p.products_id  | 1009 | Using index |
|  1 | SIMPLE    | c   | eq_ref | PRIMARY            | PRIMARY       | 4     | banggood_work.pc.categories_id |  1 | Using where |
+----+-------------+-------+--------+------------------------------+---------------------+---------+--------------------------------+------+-------------+

SELECT DISTINCT p.products_id FROM
(SELECT products_id,products_model,products_status,products_date_added
  FROM products
  ORDER BY products_date_added DESC LIMIT 200
) AS p
JOIN products_to_categories AS pc USING(products_id)
JOIN categories AS c USING(categories_id)
JOIN products_realtime_quantity AS prq ON prq.sku_or_poa = p.products_model
WHERE products_status =1 AND categories_status =1   AND prq.msg != 'Temporary out stock.'
ORDER BY products_date_added DESC LIMIT 10;

|  1 | PRIMARY   | <derived2> | ALL  | NULL             | NULL        | NULL  | NULL               |  200 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY   | prq    | ref  | ix_prg_sku_or_poa,ix_prq_msg | ix_prg_sku_or_poa   | 152   | p.products_model         |  1 | Using where; Distinct            |
|  1 | PRIMARY   | pc     | ref  | PRIMARY,categories_id    | PRIMARY       | 4     | p.products_id          | 1009 | Using index; Distinct            |
|  1 | PRIMARY   | c      | eq_ref | PRIMARY            | PRIMARY       | 4     | banggood_work.pc.categories_id |  1 | Using where; Distinct            |
|  2 | DERIVED   | products   | index  | NULL             | products_date_added | 8     | NULL               |  200 |                        |