我有两个表,它们有一个1到多个关系。假定以下结构:
users users_metadata
------------- -------------
id | email id | user_id | type | score用户可以拥有许多元数据。users表有100 k行,users_metadata表有300 k行。它可能会增长10倍,所以无论我写什么,都需要对大量的数据是最优的。
我需要编写一个sql语句,它只返回通过元数据表中的几个不同分数条件的用户电子邮件.。
// if type = 1 and if score > 75 then <1 point> else <0 points>
// if type = 2 and if score > 100 then <1 point> else <0 points>
// if type = 3 and if score > 0 then [-10 points] else <0 points>
// there are other types that we want to ignore in the score calculations如果用户通过了阈值(例如,>= 1点),那么我希望该用户在结果集中,否则我希望忽略该用户。
我尝试过用户一个存储函数/游标,它接受一个user_id并循环元数据来确定点,但是结果的执行非常慢(尽管它确实有效)。
现在我有了这个,执行大约需要1到3秒。
SELECT u.id, u.email,
(
SELECT
SUM(
IF(k.type = 1, IF(k.score > 75, 1, 0), 0) +
IF(k.type = 2, IF(k.score > 100, 1, 0), 0) +
IF(k.type = 3, IF(k.score > 0, 1, -10), 0)
)
FROM user_metadata k WHERE k.user_id = u.id
) AS total
FROM users u GROUP BY u.id HAVING total IS NOT NULL;我觉得在10倍时,这个速度会更慢。1到3秒的查询执行时间对于我已经需要的东西来说太慢了。
更理想的方法是什么?
如果我也使用一种类似于PHP的语言,那么运行两个查询(一个是从user_metadata of user_metadata of passing用户获取passing),然后再选择ids列表中的IN更好吗?
发布于 2022-01-10 23:20:59
尝试使用联接而不是关联子查询。
SELECT u.id, u.email, t.total
FROM users AS u
JOIN (
SELECT user_id, SUM(CASE type
WHEN 1 THEN score > 75
WHEN 2 THEN score > 100
WHEN 3 THEN IF(k.score > 0, 1, -10)
END) AS total
FROM user_metadata
GROUP BY user_id
HAVING total >= 1
) AS t ON u.id = t.user_id在子查询中进行分组和筛选会使联接变得更小,这将大大提高性能。
您也不需要在查询中使用GROUP BY u.id,因为这是您正在查询的表的主键;希望MySQL能够优化它。
https://stackoverflow.com/questions/70659885
复制相似问题