I have this query: -
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
The numbers inside IN () are actually identifiers of another table called tags that correspond to search keywords entered by users. In this example, the user searched for "class".
See an explanation of the output of this request here:
http://www.test.examvillage.com/Screenshot.png
The time taken for this request is 0.0536 sec.
ttagrels.id_tag in() ( ), 1-5 . , "class Available 3 "
4,2226 . 2513 .
All_Tag_Relations 6152 . ?