我有一种三元关系,叫做 ternary
这样地:
id_Offer - id_Profile - id_Skill
1 - 1 - 1
1 - 2 - 1
[and so on, there would be more registers for each id_Offer from Offer but I want to limit the example]
表profile如下所示(profile\u interest是建立profile和interest之间关系的表,仅此而已):
id_Profile - profile_name
1 - profile-1
2 - profile-2
3 - profile-3
因此,当我进行以下查询时,我添加的or子句越多,查询的性能就越差,从~0.1-0.2秒开始,这是我进行的任何其他查询得到的结果,最多1.5秒。
SELECT DISTINCT ternary_table.id_profile, COUNT(distinct profile_interest.id_interest) as matching
FROM ternary_table INNER JOIN profile ON ternary_table.id_profile=profile.id_profile
INNER JOIN profile_interest ON profile.id_profile=profile_interest.id_profile
WHERE profile_interest.id_interest= '1'
OR profile_interest.id_interest = '2'
OR profile_interest.id_interest = '3'
OR profile_interest.id_interest = '14'
OR profile_interest.id_interest = '15'
OR profile_interest.id_interest = '16'
GROUP BY(ternary_table.id_profile)
ORDER BY matching DESC;
我已尝试将字段配置文件\u interest.id\u interest设置为索引列,其中包含: CREATE INDEX filter_interest ON profile_interest(id_interest );
没有任何改善。数据库的重量不到1千兆字节,是一个非常小的数据库,大约有15个表,所以我想知道是否有任何方法可以缩短查询延迟。
edit:为了添加更多信息,我之所以担心这个问题,是因为这个数据的唯一目的是连接到api,因此sql中的任何延迟都会延迟对这个数据的每次调用。
edit1:添加了explain输出,删除了first distinct,因为这是不必要的
+----+-------------+---------------------+------------+--------+------------------------------------------------+------------+---------+------------------------------------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------------+------------+--------+------------------------------------------------+------------+---------+------------------------------------+------+----------+-----------------------------------------------------------+
| 1 | SIMPLE | profile_interest | NULL | range | PRIMARY,id_interest,filter_interest | id_interest | 202 | NULL | 40 | 100.00 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | perfil | NULL | eq_ref | PRIMARY | PRIMARY | 202 | BBDD.profile_interest.id_perfil | 1 | 100.00 | Using index |
| 1 | SIMPLE | oferta_skill_perfil | NULL | ref | PRIMARY,id_skill,id_perfil | id_perfil | 202 | BBDD.profile_interest.id_perfil | 4609 | 100.00 | Using index |
+----+-------------+---------------------+------------+--------+------------------------------------------------+------------+---------+------------------------------------+------+----------+-----------------------------------------------------------+
编辑2:添加了每个请求的表创建
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE profile (
id_profile VARCHAR(200) NOT NULL,
name_profile VARCHAR(200),
type_profile VARCHAR(200),
PRIMARY KEY (id_profile)
);
CREATE TABLE ternary (
id_oferta VARCHAR(200) NOT NULL,
id_skill VARCHAR(200) NOT NULL,
id_profile VARCHAR(200) NOT NULL,
ranking_skill DOUBLE NOT NULL,
PRIMARY KEY (id_oferta, id_skill, id_profile),
FOREIGN KEY (id_oferta) REFERENCES oferta(id_oferta),
FOREIGN KEY (id_skill) REFERENCES skill(id_skill),
FOREIGN KEY (id_profile) REFERENCES profile(id_profile)
);
CREATE TABLE interest (
id_interest VARCHAR(200) NOT NULL,
name_interes VARCHAR(200),
PRIMARY KEY (id_interest)
);
CREATE TABLE profile_interest (
id_profile VARCHAR(200) NOT NULL,
id_interest VARCHAR(200) NOT NULL,
PRIMARY KEY (id_profile, id_interest),
FOREIGN KEY (id_profile) REFERENCES profile(id_profile),
FOREIGN KEY (id_interest) REFERENCES interes(id_interest)
);
4条答案
按热度按时间bfnvny8b1#
你可以试着这样写:
为了让它工作,你需要一个索引
profile_interest(id_profile, id_interest)
.编辑:
如果只需要匹配的行,则可以添加:
zpf6vheq2#
没有答案。评论时间太长。。。
fwiw,我觉得这更容易阅读。。。
现在,如果我们能看到show create table语句以及对它的解释就好了。
8qgya5xd3#
您的查询可以简化为:
fwzugrvs4#
以及
profile_interest
需要INDEX(id_interest, id_profile)
按这个顺序。我曾经
EXISTS
只是因为这似乎是触摸其他table的真正目的。但也许他们不需要被触碰?减速是由我所说的“爆炸-内爆”或“膨胀-收缩”引起的。当你
JOIN
一些表(导致更多中间行),然后GROUP BY
回到你开始的状态。摆脱它的技巧是首先集中精力做聚合(COUNT
,在你的情况下),那么JOIN
根据需要。EXISTs
如果只需要检查是否存在,而不是实际查找所有4609行,则速度要快得多。profile_interest
似乎是一个多对多Map表。请看我的提示。注意,它推荐了我上面建议的索引。
通常id是整数;为什么你有
VARCHAR(200)
? 这种长字符串没有明显的来源。