如何优化这个SQL呢 MySQL

我来答

Awesome Database

2023-12-08

如何优化这个SQL呢 MySQL

10M

mysql

SELECT wp_posts.ID
FROM wp_posts JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
WHERE
wp_term_relationships.term_taxonomy_id IN (118,124,127,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,906,912,913,914,915,917,918,919,920,921,923,926,927,928,929,932,933,935,937,938,939,972,974,975,976,979,980,981,983,984,986,987,988,989,991,992,993,994,997,998,999,1000,1001,1002,1003,1006,1008,1009,1010,1011,1014,1017,1018,1019,1023,1024,1026,1030,1032,1033,1297,1304,1319,1324,1325,1327,1331,1333,1335,1336,1338,1339,1340,1341,1348,1413,1686,2037,2353,2355,3047,3643,4437,5056,6662,8952,10172,12533,12534,12536,12537,15245,15782,28583,31766,31778,31779,31783,31784,31785,31803,31804,31807,31808,31813,31814,31818,31820,31821,31822,31826,31845,34582,34583,34586,34587,34591,34595,34990,34991,34992,34993,34995,34998,34999,35000,35001,35002,35003,35004,35008,35009,35010,35011,35026,35027,35028,35029,35030,35032,35033,35035,35036,35037,35038,35039,50785,51893,52466,56645,59235,145481,145482,145483)
AND
wp_posts.post_type = 'post'
AND wp_posts.post_status in ('publish','acf-disabled')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10;

执行计划

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp_term_relationships	range	PRIMARY,term_taxonomy_id	term_taxonomy_id	8	NULL	74483	Using where; Using index; Using temporary; Using filesort
1	SIMPLE	wp_posts	eq_ref	PRIMARY,type_status_date,idx_ps_pt_pdg,idx_pt_ps_pdd,idx_pt_pdd_ps	PRIMARY	8	bnnproddb.wp_term_relationships.object_id	1	Using where

我来答

添加附件

问题补充

9条回答

默认

最新

广州_老虎刘

可以试试(post_type,post_date , post_status) 3字段联合索引; 同时把GROUP BY wp_posts.ID去掉(id字段应该是主键)

有用 2

Awesome Database

题主

2023-12-10

谢回复，这里wp_posts.ID是主键，但是去掉group by，会导致返回的结果集不一致，wp_term_relationships有一个联合主键(object_id,term_taxonomy_id),其中有一组数据数集是(1930747,613)和(1930747,645)，去掉group by，返回的ID就有两个1930747，

Awesome Database

题主

2023-12-10

谢大佬，给了思路，已经解决了，把去重的操作给wp_term_relationships来完成，直接7ms完成。原来是3.1秒

Mjwlan

这样吗：

SELECT wp_posts.ID
FROM wp_posts
JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
WHERE wp_term_relationships.term_taxonomy_id IN (
SELECT term_taxonomy_id
FROM (VALUES (118), (124), (127), (...), (145483)) AS t(term_taxonomy_id)
)
AND wp_posts.post_type = 'post'
AND wp_posts.post_status IN ('publish', 'acf-disabled')
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10;