关于系统:
-有些导师会创建课程和课程包
-采用基于标签的搜索方法。标签关系是在新导师注册和导师创建包时创建的(这使得导师和包可搜索)。有关详细信息,请查看该部分
标签在这个系统中是如何工作的
?下面。
下面是相关的查询
有人能帮我推荐使用临时表的方法吗?我们已经索引了所有相关的字段,并且看起来这是使用此方法可能的最短时间:
SELECT SUM(DISTINCT( t.tag LIKE "%Dictatorship%"
OR tt.tag LIKE "%Dictatorship%"
OR ttt.tag LIKE "%Dictatorship%" )) AS key_1_total_matches
,
SUM(DISTINCT( t.tag LIKE "%democracy%"
OR tt.tag LIKE "%democracy%"
OR ttt.tag LIKE "%democracy%" )) AS key_2_total_matches
,
COUNT(DISTINCT( od.id_od )) AS
tutor_popularity,
CASE
WHEN ( IF(( wc.id_wc > 0 ), ( wc.wc_api_status = 1
AND wc.wc_type = 0
AND wc.class_date > '2010-06-01 22:00:56'
AND wccp.status = 1
AND ( wccp.country_code = 'IE'
OR wccp.country_code IN ( 'INT' )
) ), 0)
) THEN 1
ELSE 0
END AS 'classes_published'
,
CASE
WHEN ( IF(( lp.id_lp > 0 ), ( lp.id_status = 1
AND lp.published = 1
AND lpcp.status = 1
AND ( lpcp.country_code = 'IE'
OR lpcp.country_code IN ( 'INT' )
) ), 0)
) THEN 1
ELSE 0
END AS 'packs_published',
td . *,
u . *
FROM tutor_details AS td
JOIN users AS u
ON u.id_user = td.id_user
LEFT JOIN learning_packs_tag_relations AS lptagrels
ON td.id_tutor = lptagrels.id_tutor
LEFT JOIN learning_packs AS lp
ON lptagrels.id_lp = lp.id_lp
LEFT JOIN learning_packs_categories AS lpc
ON lpc.id_lp_cat = lp.id_lp_cat
LEFT JOIN learning_packs_categories AS lpcp
ON lpcp.id_lp_cat = lpc.id_parent
LEFT JOIN learning_pack_content AS lpct
ON ( lp.id_lp = lpct.id_lp )
LEFT JOIN webclasses_tag_relations AS wtagrels
ON td.id_tutor = wtagrels.id_tutor
LEFT JOIN webclasses AS wc
ON wtagrels.id_wc = wc.id_wc
LEFT JOIN learning_packs_categories AS wcc
ON wcc.id_lp_cat = wc.id_wp_cat
LEFT JOIN learning_packs_categories AS wccp
ON wccp.id_lp_cat = wcc.id_parent
LEFT JOIN order_details AS od
ON td.id_tutor = od.id_author
LEFT JOIN orders AS o
ON od.id_order = o.id_order
LEFT JOIN tutors_tag_relations AS ttagrels
ON td.id_tutor = ttagrels.id_tutor
LEFT JOIN tags AS t
ON t.id_tag = ttagrels.id_tag
LEFT JOIN tags AS tt
ON tt.id_tag = lptagrels.id_tag
LEFT JOIN tags AS ttt
ON ttt.id_tag = wtagrels.id_tag
WHERE ( u.country = 'IE'
OR u.country IN ( 'INT' ) )
AND CASE
WHEN ( ( tt.id_tag = lptagrels.id_tag )
AND ( lp.id_lp > 0 ) ) THEN lp.id_status = 1
AND lp.published = 1
AND lpcp.status = 1
AND ( lpcp.country_code = 'IE'
OR lpcp.country_code IN (
'INT'
) )
ELSE 1
END
AND CASE
WHEN ( ( ttt.id_tag = wtagrels.id_tag )
AND ( wc.id_wc > 0 ) ) THEN wc.wc_api_status = 1
AND wc.wc_type = 0
AND
wc.class_date > '2010-06-01 22:00:56'
AND wccp.status = 1
AND ( wccp.country_code = 'IE'
OR wccp.country_code IN (
'INT'
) )
ELSE 1
END
AND CASE
WHEN ( od.id_od > 0 ) THEN od.id_author = td.id_tutor
AND o.order_status = 'paid'
AND CASE
WHEN ( od.id_wc > 0 ) THEN od.can_attend_class = 1
ELSE 1
END
ELSE 1
END
AND ( t.tag LIKE "%Dictatorship%"
OR t.tag LIKE "%democracy%"
OR tt.tag LIKE "%Dictatorship%"
OR tt.tag LIKE "%democracy%"
OR ttt.tag LIKE "%Dictatorship%"
OR ttt.tag LIKE "%democracy%" )
GROUP BY td.id_tutor
HAVING key_1_total_matches = 1
AND key_2_total_matches = 1
ORDER BY tutor_popularity DESC,
u.surname ASC,
u.name ASC
LIMIT 0, 20
问题
上述查询返回的结果是正确的(逻辑按预期工作),但对于较重的数据,查询所花费的时间会显著增加,对于当前的数据,与正常的查询时间(0.005-0.0002秒)相比,我所拥有的时间大约为10秒,这使得它完全不可用。
有人在我之前的问题中建议做以下工作:
-
创建一个临时表并在此处插入最终结果集中可能出现的所有相关数据
-
在此表上运行多个更新,一次连接一个所需表,而不是同时连接所有表
-
最后对此临时表执行查询以提取最终结果
所有这些都是在一个存储过程中完成的,最终的结果已经通过了单元测试,并且正在迅速地燃烧。
到现在为止,我还没有和临时桌子一起工作过。只有我能得到一些提示,一些示意图,这样我才能从…
这个查询有问题吗?
执行时间超过10秒的原因是什么?
标签在这个系统中是如何工作的?
-
当导师注册时,将输入标签,并根据导师的详细信息(如姓名等)创建标签关系。
-
当导师创建包时,再次输入标记,并根据包的详细信息(如包名称、描述等)创建标记关系。
-
存储在导师关系中的导师的标记关系和存储在学习关系中的包的标记关系。所有单个标记都存储在标记表中。
