SELECT SUM(DISTINCT(ttagrels.id_tag IN (1816, 2642, 1906, 1398,
2436, 2940, 1973, 2791, 1389))) AS
key_1_total_matches,
IF((od.id_od > 0), COUNT(DISTINCT(od.id_od)), 0) AS
tutor_popularity,
td.*,
u.*
FROM tutor_details AS td
JOIN users AS u
ON u.id_user = td.id_user
JOIN all_tag_relations AS ttagrels
ON td.id_tutor = ttagrels.id_tutor
LEFT JOIN learning_packs AS lp
ON ttagrels.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 AS wc
ON ttagrels.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
WHERE (u.country = 'IE'
OR u.country IN ('INT'))
AND u.status = 1
AND CASE
WHEN (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 (wc.id_wc > 0) THEN wc.wc_api_status = 1
AND wc.id_status = 1
AND wc.wc_type = 0
AND
wc.class_date > '2010-06-16 11:44:40'
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 (ttagrels.id_tag IN (1816, 2642, 1906, 1398,
2436, 2940, 1973, 2791, 1389))
GROUP BY td.id_tutor
HAVING key_1_total_matches = 1
ORDER BY tutor_popularity DESC,
u.surname ASC,
u.name ASC
LIMIT 0, 20
的数字()实际上是另一个表称为标签的IDS其中与用户输入的搜索关键字匹配。在这个例子中,用户搜索了“class”。
此查询所花费的时间是0.0536秒
但是,如果值在ttagrels.id_tag()的数量增加(当用户输入更多的搜索关键字时),执行时间上升到1-5秒左右。例如,如果用户搜索“班级可用于导师和学生每天3次”
执行时间为4.2226秒。此查询的说明查询输出在行下包含2513。
All_Tag_Relations表中共有6,152条记录。是否有进一步的优化可能?
+1
我知道机器不关心SQL代码中的换行符,但是如果你把它们放进去,它会使* *更易读。 –
2010-06-23 08:11:55
+0
yes我已经添加了 –
2010-06-23 08:18:25