mysql 优化查询in_Mysql的优化选择查询与IN（）子句

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