MySql排序结果包括SELECT IF() as Alice

Question

我有两个表products和products_actions

在产品中，我存储普通数据，如id、名称、制造商、价格、date_added

在product_actions中，我像这样存储数据: id，product_id，store_id，operator，status_id

然后问题是，当我在DataTables中显示数据并尝试按store_id排序时，需要很长时间，比如3-4秒(如果我按名称或id排序，花费的时间不到半秒)，即使我在product_actions中索引product_id，products_actions中只有2k条记录，products中只有14k条记录

当我使用IF as Alice时，如何解决排序问题，看起来没有索引

    SELECT p.id, p.name, p.mpn, p.price, p.comment, p.status, p.date_added, m.name AS manufacture,
(SELECT IF(pa.store_id = 9, pa.status, 'null') FROM products_actions pa WHERE p.id = pa.product_id AND pa.store_id = 9) AS store_1,
(SELECT IF(pa.store_id = 4, pa.status, 'null') FROM products_actions pa WHERE p.id = pa.product_id AND pa.store_id = 4) AS store_2,
(SELECT IF(pa.store_id = 5, pa.status, 'null') FROM products_actions pa WHERE p.id = pa.product_id AND pa.store_id = 5) AS store_3
FROM products p
LEFT JOIN manufacture m ON p.manufacture = m.id

Answer 1

因为我从products_actions选择了store_id和status

(SELECT IF(pa.store_id = 9, pa.status, 'null') FROM products_actions pa WHERE p.id = pa.product_id AND pa.store_id = 9) AS store_1

我也应该为它们建立索引，而不是像我那样只使用product_id