我正在努力改进一个非常慢的查询。
基本上，我需要从附属产品列表中为每个匹配（布尔值）搜索查询的数据源选择最便宜的
查询按预期工作，但在1.5mil行表上需要很长时间（2+sec）
select p.* from (
SELECT
*
FROM
products a
WHERE
display_price = (
SELECT
MIN(display_price)
FROM
products b
WHERE
b.datafeed_id = a.datafeed_id
AND
(match(b.name) against ('+saucony +"ride iso 2" -women* -kid* -junior*' in boolean mode))
)
AND
(match(a.name) against ('+saucony +"ride iso 2" -women* -kid* -junior*' in boolean mode))
group by
a.datafeed_id
)
as p
where
p.is_custom = 0
order by
p.datafeed_id
limit 50
你能帮我改进一下吗？
我尝试过使用内部连接，但总是得到错误的结果（不是预期的最便宜的产品）
表数据示例
+-----------+-------------------------+---------------+--------------+-------------------------------------------------------------------------------------------+---------------------------------------------------+---------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------+-----------+---------------+-------------+---------------+---------------------+---------------------+-------------+-----------------------+--------------------------------------------------------------+-----------+-----------+-----------------------------------------------------------------------------------+-------------+-----------------+---------------------+------------+------------+------------+------------+------------+------------+----------------------+-------------------------------+
| id | mykey | merchant_name | model_number | name | description | thumb_url | image_url | deep_link | delivery_time | in_stock | rrp_price | display_price | is_preorder | delivery_cost | created_at | updated_at | datafeed_id | category | subcategory | is_custom | is_manual | autoslug | category_id | sub_category_id | network_product_key | ws_api_key | used_price | userfield1 | userfield2 | userfield3 | userfield4 | api_refresh_failures | api_keywords |
+-----------+-------------------------+---------------+--------------+-------------------------------------------------------------------------------------------+---------------------------------------------------+---------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------+-----------+---------------+-------------+---------------+---------------------+---------------------+-------------+-----------------------+--------------------------------------------------------------+-----------+-----------+-----------------------------------------------------------------------------------+-------------+-----------------+---------------------+------------+------------+------------+------------+------------+------------+----------------------+-------------------------------+
| 209322441 | 32_15724 | JackRabbit | 609465417986 | Men's Altra Solstice Running Shoe - Color: Grey/Orange (Regular Width) - Size: 8.5 | Solstice Running Shoe | | https://cdn.jackrabbit.com/media/catalog/product/m/e/mens-altra-solstice-running-shoe-color-greyblue-regular-width-size-7-609465332132-01.2351_1.jpg | https://click.linksynergy.com/link?id=bXlQtQGle9o&offerid=445404.15724&type=15&murl=https%3A%2F%2Fwww.jackrabbit.com%2Fmen-s-altra-solstice-running-shoe-afm1836-2.html | | 0 | 0.00 | 89.95 | 0 | 0.00 | 2019-08-13 07:00:43 | 2019-08-13 07:00:43 | 32 | Apparel & Accessories | Shoes~~Athletic Shoes & Sneakers~~Sneakers~~Running Shoes | 0 | NULL | men-s-altra-solstice-running-shoe-color-grey-orange-regular-width-size-8-5 | 24746552 | 24746581 | NULL | NULL | NULL | | | | | 0 | NULL |
| 209444684 | 17_887687492430 | NewBalance | 887687492430 | New Balance 4128 Men's Momentum Long Sleeve - Cobalt Blue (MRT4128CBT) | New Balance 4128 Men's Momentum Long Sleeve | | http://s7d5.scene7.com/is/image/NB/mrt4128cbt_nb_60_i?$google$ | https://click.linksynergy.com/link?id=bXlQtQGle9o&offerid=691495.887687492430&type=15&murl=http%3A%2F%2Ftracking.searchmarketing.com%2Fclick.asp%3Faid%3D172000630002315714 | | 0 | 0.00 | 36.99 | 0 | 0.00 | 2019-08-13 07:02:37 | 2019-08-13 07:02:37 | 17 | Men | Apparel~~Long Sleeve Shirts~~Running | 0 | NULL | new-balance-4128-men-s-momentum-long-sleeve-cobalt-blue-mrt4128cbt- | 24747216 | 24747104 | NULL | NULL | NULL | | | | | 0 | NULL |
| 210677909 | amazon-co-uk_B07JKJVNWS | amazon.co.uk | NULL | New Balance Women's 1500v4 Boa Running Shoes | New Balance Women's 1500v4 Boa Running Shoes | https://images-eu.ssl-images-amazon.com/images/I/41nngdBxrmL._SL75_.jpg | https://images-eu.ssl-images-amazon.com/images/I/41nngdBxrmL.jpg | https://www.amazon.co.uk/New-Balance-Womens-1500v4-Running/dp/B07JKJVNWS?SubscriptionId=AKIAJ2HRNZ42ECGURRQQ&tag=runshogur-21&linkCode=xm2&camp=2025&creative=165953&creativeASIN=B07JKJVNWS | NULL | 1 | NULL | 55.93 | NULL | NULL | 2019-08-15 14:13:42 | 2019-08-15 14:13:42 | 41 | NULL | NULL | 0 | NULL | new-balance-women-s-1500v4-boa-running-shoes | NULL | NULL | B07JKJVNWS | amazon-co- | 0.00 | NULL | NULL | NULL | NULL | 0 | new balance 1500v4 women shoe |
| 209491456 | 17_889516760314 | NewBalance | 889516760314 | New Balance 61021 Men's Trinamic Short Sleeve Top - Red/Black (MT61021AMH) | New Balance 61021 Men's Trinamic Short Sleeve Top | | http://s7d5.scene7.com/is/image/NB/mt61021amh_nb_40_i?$google$ | https://click.linksynergy.com/link?id=bXlQtQGle9o&offerid=691495.889516760314&type=15&murl=http%3A%2F%2Ftracking.searchmarketing.com%2Fclick.asp%3Faid%3D172000630001018890 | | 0 | 0.00 | 74.99 | 0 | 0.00 | 2019-08-13 07:02:51 | 2019-08-13 07:02:51 | 17 | Men | Clothing~~Short Sleeve & Sleeveless Shirts~~Performance | 0 | NULL | new-balance-61021-men-s-trinamic-short-sleeve-top-red-black-mt61021amh- | 24747216 | 24747297 | NULL | NULL | NULL | | | | | 0 | NULL |
| 209704762 | 38_332299573 | SportChek | 332299573 | Under Armour Men's ColdGear Reactor Shorts | Under Armour Men’s ColdGear Reactor Shorts | https://fgl.scene7.com/is/image/FGLSportsLtd/332299564_01_a?hei=800&wid=800&resMode=sharp2&op_sharpen=1 | https://fgl.scene7.com/is/image/FGLSportsLtd/332299564_01_a?hei=800&wid=800&resMode=sharp2&op_sharpen=1 | https://www.pntra.com/t/Qz9HRURIP0NGS0pCQz9HRURI?website=250687&url=https%3A%2F%2Fsportchek.affiliatetechnology.com%2Fredirect.php%3Fnt_id%3D2%26url%3Dhttps%3A%2F%2Fwww.sportchek.ca%2Fproduct%2F332299564.html%23332299564%3D332299573 | | 0 | 0.00 | 43.88 | 0 | 0.00 | 2019-08-13 07:04:39 | 2019-08-13 07:04:39 | 38 | | | 0 | NULL | under-armour-men-s-coldgear-reactor-shorts | -1 | -1 | NULL | NULL | NULL | | | | | 0 | NULL |
| 209323700 | 32_18027 | JackRabbit | 609465418261 | Men's Altra Lone Peak 4.0 Trail Running Shoe - Color: Black/Red (Regular Width) - Size: 9 | Lone Peak 4.0 Trail Running Shoe | | https://cdn.jackrabbit.com/media/catalog/product/m/e/mens-altra-lone-peak-4.0-trail-running-shoe-color-greyorange-regular-width-size-14-609465354726-01.2544_1.jpg | https://click.linksynergy.com/link?id=bXlQtQGle9o&offerid=445404.18027&type=15&murl=https%3A%2F%2Fwww.jackrabbit.com%2Fmen-s-altra-lone-peak-4-0-trail-running-shoe-afm1855f-21.html | | 0 | 0.00 | 119.95 | 0 | 0.00 | 2019-08-13 07:00:44 | 2019-08-13 07:00:44 | 32 | Apparel & Accessories | Shoes~~Athletic Shoes & Sneakers~~Sneakers~~Running Shoes | 0 | NULL | men-s-altra-lone-peak-4-0-trail-running-shoe-color-black-red-regular-width-size-9 | 24746552 | 24746581 | NULL | NULL | NULL | | | | | 0 | NULL |
| 209370278 | 17_887057044573 | NewBalance | 887057044573 | Cobb Hill Poppy Women's Shop All Shoes - Black (CAG10BK) | Cobb Hill Poppy Women's Shop All Shoes | | http://s7d5.scene7.com/is/image/NB/cag10bk_ch_02_i?$google$ | https://click.linksynergy.com/link?id=bXlQtQGle9o&offerid=691495.887057044573&type=15&murl=http%3A%2F%2Ftracking.searchmarketing.com%2Fclick.asp%3Faid%3D172000630000396733 | | 0 | 0.00 | 89.99 | 0 | 0.00 | 2019-08-13 07:02:13 | 2019-08-13 07:02:13 | 17 | Women | Shoes~~Casuals~~Cobb Hill By New Balance~~Shop All Cobb Hill | 0 | NULL | cobb-hill-poppy-women-s-shop-all-shoes-black-cag10bk- | 24746991 | 24747059 | NULL | NULL | NULL | | | | | 0 | NULL |
| 210680592 | amazon-co-uk_B07DRMQGC7 | amazon.co.uk | NULL | Brooks Men's Addiction 13 Running Shoes | Brooks Men's Addiction 13 Running Shoes | https://images-eu.ssl-images-amazon.com/images/I/41%2Bqhn33SVL._SL75_.jpg | https://images-eu.ssl-images-amazon.com/images/I/41%2Bqhn33SVL.jpg | https://www.amazon.co.uk/Brooks-Mens-Addiction-Running-Shoes/dp/B07DRMQGC7?SubscriptionId=AKIAJ2HRNZ42ECGURRQQ&tag=runshogur-21&linkCode=xm2&camp=2025&creative=165953&creativeASIN=B07DRMQGC7 | NULL | 1 | NULL | 193.69 | NULL | NULL | 2019-08-15 14:34:47 | 2019-08-15 14:34:47 | 41 | NULL | NULL | 0 | NULL | brooks-men-s-addiction-13-running-shoes | NULL | NULL | B07DRMQGC7 | amazon-co- | 0.00 | NULL | NULL | NULL | NULL | 0 | brooks addiction 13 men shoe |
| 209522750 | 17_889116380196 | NewBalance | 889116380196 | 574 Hook and Loop Kids' Infant Lifestyle Shoes - Blue, Black, Orange (KG574MTI) | 574 Hook and Loop Kids' Infant Lifestyle Shoes | | http://s7d5.scene7.com/is/image/NB/kg574mti_nb_14_i?$google$ | https://click.linksynergy.com/link?id=bXlQtQGle9o&offerid=691495.889116380196&type=15&murl=http%3A%2F%2Ftracking.searchmarketing.com%2Fclick.asp%3Faid%3D172000630000618712 | | 0 | 0.00 | 44.99 | 0 | 0.00 | 2019-08-13 07:03:01 | 2019-08-13 07:03:01 | 17 | Kids | Boys Shoes~~Infant (size 2 - 5, 5.5 - 10)~~Infant Lifestyle | 0 | NULL | 574-hook-and-loop-kids-infant-lifestyle-shoes-blue-black-orange-kg574mti- | 24747441 | 24747457 | NULL | NULL | NULL | | | | | 0 | NULL |
| 209401692 | 17_887057216642 | NewBalance | 887057216642 | Aravon Pauline-AR Women's by New Balance Shoes - Black (AAZ11BK) | Aravon Pauline-AR Women's by New Balance Shoes | | http://s7d5.scene7.com/is/image/NB/aaz11bk_ara_02_i?$google$ | https://click.linksynergy.com/link?id=bXlQtQGle9o&offerid=691495.887057216642&type=15&murl=http%3A%2F%2Ftracking.searchmarketing.com%2Fclick.asp%3Faid%3D172000630000635005 | | 0 | 0.00 | 249.99 | 0 | 0.00 | 2019-08-13 07:02:23 | 2019-08-13 07:02:23 | 17 | Women | Shoes~~Casuals~~Aravon By New Balance | 0 | NULL | aravon-pauline-ar-women-s-by-new-balance-shoes-black-aaz11bk- | 24746991 | 24747102 | NULL | NULL | NULL | | | | | 0 | NULL |
+-----------+-------------------------+---------------+--------------+-------------------------------------------------------------------------------------------+---------------------------------------------------+---------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+----------+-----------+---------------+-------------+---------------+---------------------+---------------------+-------------+-----------------------+--------------------------------------------------------------+-----------+-----------+-----------------------------------------------------------------------------------+-------------+-----------------+---------------------+------------+------------+------------+------------+------------+------------+----------------------+-------------------------------+
10 rows in set (1.17 sec)
谢谢您
最佳答案
@朱利奥你的解决方案似乎很有效。也许covering index会加速一点。
如果您使用的是MySQL 8，我建议使用CTE来删除多余的过滤器表达式（并最终获得进一步的加速），如下所示：
WITH
FILTERED_PRODUCTS AS (
SELECT
p.*
FROM
products p
WHERE
(match(p.name) against('+saucony +"ride iso 2" -women* -kid* -junior*' IN BOOLEAN MODE))
AND p.is_custom = 0
),
FILTERED_PRODUCT_PRICES AS (
SELECT
MIN(p.display_price) MIN_PRICE,
p.datafeed_id
FROM
FILTERED_PRODUCTS p
GROUP BY p.datafeed_id
)
SELECT
p.datafeed_id,
p.display_price,
p.*
FROM
FILTERED_PRODUCTS p
JOIN
FILTERED_PRODUCT_PRICES f ON
p.display_price = f.MIN_PRICE
AND p.datafeed_id = f.datafeed_id
GROUP BY p.datafeed_id
ORDER BY p.datafeed_id
我觉得最后一个
GROUP BY语句应该被一个更确定的选择替换，即使它需要一个附加的subselect语句。