mysql查询需要1+分钟才能完成
此查询
SELECT * FROM kp_landing_page lp
WHERE lp.parent = '7' AND (
SELECT COUNT(*) FROM kp_landing_page_product lpp
WHERE lpp.landing_page_id = lp.landing_page_id
AND lpp.productid = '6176'
) != 0大约需要1+分钟才能完成，我试着这样重写它
SELECT * FROM kp_landing_page lp
INNER JOIN (SELECT COUNT(*) FROM kp_landing_page_product lpp
WHERE lpp.landing_page_id = lp.landing_page_id
AND lpp.productid = '6176') != 0
WHERE lp.parent = '7'虽然这要快得多(3-4秒)，但它没有满足我所需的数据，即使所有的表都保持不变，并且在phpmyadmin中执行这个查询会给我提供与旧查询相同的数据。它只会给出以下错误
The current selection does not contain a unique column.
Functions such as raster edits, checkboxes, Edit,
Copy and Delete are not available.关于如何优化这一点有什么想法吗？不一定要非常快，1+，慢一分钟。
编辑
对旧查询运行explain
EXPLAIN SELECT * FROM kp_landing_page lp WHERE lp.parent = '7'
AND ( SELECT COUNT(*) FROM kp_landing_page_product lpp
WHERE lpp.landing_page_id = lp.landing_page_id
AND lpp.productid = '6176' ) != 0为我提供了以下内容
1 id
PRIMARY select_Type
lp table
ALL type
NULL possible_keys
NULL keys
NULL key_len
NULL ref
233 rows
Using where extra
---
2
DEPENDENT SUBQUERY
lpp
ref
landing_page_id
landing_page_id
4
kerstpakketonline.lp.landing_page_id
437
Using where编辑2
更多信息
这段代码(PHP)就是速度减慢的地方
$landingPages = array();
$qGetMainPages = $connection->query("SELECT *
FROM kp_landing_page WHERE parent = 0");
foreach ($qGetMainPages->rows as $mainPage) {
$qGetSubPages = $connection->query("
SELECT lp.* FROM kp_landing_page lp
WHERE lp.parent = '" . (int)$mainPage['landing_page_id'] . "' AND (
SELECT COUNT(*) FROM kp_landing_page_product lpp
WHERE lpp.landing_page_id = lp.landing_page_id
AND lpp.productid = " . (int)$row['productID'] . "
) != 0
");
foreach ($qGetSubPages->rows as $subPage) {
$landingPages[$mainPage['title']][] = $subPage['title'];
}
}似乎正在对每个产品执行x数量的查询，从而导致o(n)问题？
编辑3
使用SHOW CREATE TABLE on both tables
-- kp_landing_page_product
kp_landing_page_product CREATE TABLE kp_landing_page_product ( id int(11) NOT NULL AUTO_INCREMENT, landing_page_id int(11) NOT NULL, productid int(11) NOT NULL, PRIMARY KEY (id), KEY landing_page_id (landing_page_id), KEY productid (productid), CONSTRAINT landing_page_id_2 FOREIGN KEY (landing_page_id) REFERENCES kp_landing_page (landing_page_id) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=522630 DEFAULT CHARSET=utf8
-- kp_landing_page
kp_landing_page CREATE TABLE kp_landing_page ( landing_page_id int(11) NOT NULL AUTO_INCREMENT, title varchar(255) COLLATE utf8mb4_bin NOT NULL, title_multi varchar(255) COLLATE utf8mb4_bin NOT NULL, rewrite varchar(255) COLLATE utf8mb4_bin NOT NULL, active tinyint(4) NOT NULL DEFAULT 1, parent int(11) NOT NULL DEFAULT 0, ordering int(11) NOT NULL, show_as_filter tinyint(4) NOT NULL DEFAULT 0, popular tinyint(4) NOT NULL DEFAULT 0, color_code varchar(255) COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (landing_page_id) ) ENGINE=InnoDB AUTO_INCREMENT=247 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
kp_landing_page_product总共包含160854行
kp_landing_page总共包含233行
使用此SELECT * FROM kp_landing_page WHERE parent = 0查询，kp_landing_page将返回9行
kp_landing_page_product使用以下命令返回大约40行
SELECT * FROM kp_landing_page lp WHERE lp.parent = '7' AND ( SELECT COUNT(*) FROM kp_landing_page_product lpp WHERE lpp.landing_page_id = lp.landing_page_id AND lpp.productid = '6176' ) != 0查询，此值可根据提供的productid进行更改
Stack Overflow用户
发布于 2021-11-12 16:27:19
索引：
lp: INDEX(landing_page_id)
lpp: INDEX(landing_page_id, productid) -- either order is OK使用EXISTS (也称为"semi-join")代替
AND (
SELECT COUNT(*) FROM kp_landing_page_product lpp
WHERE lpp.landing_page_id = lp.landing_page_id
AND lpp.productid = '6176'
) != 0-->
AND EXISTS ( SELECT 1 FROM kp_landing_page_product AS lpp
WHERE lpp.landing_page_id = lp.landing_page_id
AND lpp.productid = '6176' )当找到第一个计数时，EXISTS将停止；COUNT(*)可能需要做更多的工作才能找出确切的计数。
(如果您希望进一步讨论，请提供两个表的SHOW CREATE TABLE、每个表的大小(大约行数)和查询返回的行数。)
https://stackoverflow.com/questions/69940084
复制相似问题
腾讯云开发者
