In finding some results I've found following two ways, but unable to decide which query has better performance. Hope someone will suggest!
SELECT SQL_CALC_FOUND_ROWS *, MATCH (m.firstname, m.lastname) AGAINST ('*keyword1* *keyword2* *keyword3*' IN BOOLEAN MODE) AS relavance FROM members m WHERE m.status = 1 AND m.membership > 1 AND (SELECT count(media_id) FROM arts AS media WHERE media.active = 1 AND media.owner = m.mem_id) > 0 AND MATCH (m.firstname, m.lastname) AGAINST ('*keyword1* *keyword2* *keyword3*' IN BOOLEAN MODE) ORDER BY relavance DESC, m.firstname ASC, m.lastname ASC
SELECT SQL_CALC_FOUND_ROWS *, (SELECT count(media_id) FROM arts AS media WHERE media.active = 1 AND media.owner = m.mem_id) AS arts_count, MATCH (m.firstname, m.lastname) AGAINST ('*keyword1* *keyword2* *keyword3*' IN BOOLEAN MODE) AS relavance FROM members m WHERE m.status = 1 AND m.membership > 1 AND MATCH (m.firstname, m.lastname) AGAINST ('*keyword1* *keyword2* *keyword3*' IN BOOLEAN MODE) HAVING arts_count > 0 ORDER BY relavance DESC, m.firstname ASC, m.lastname ASC
Edit:
I've found another way too, now comparison is between three queries. Following is the new query which I've found and from all three queries which is the better in performance point of view ?
SELECT SQL_CALC_FOUND_ROWS *, MATCH (m.firstname, m.lastname) AGAINST ('*keyword1* *keyword2* *keyword3*' IN BOOLEAN MODE) AS relavance FROM members m LEFT JOIN (SELECT count(media_id) AS arts_count, owner AS ar_owner FROM arts GROUP BY media_id) AS ar ON ar.ar_ar_owner = m.owner WHERE m.status = 1 AND m.membership > 1 AND MATCH (m.firstname, m.lastname) AGAINST ('*keyword1* *keyword2* *keyword3*' IN BOOLEAN MODE) AND ar.arts_count > 0 ORDER BY relavance DESC, m.firstname ASC, m.lastname ASC
Objective of the Queries: To find users for matching keywords and having at-least one uploaded image/art and some more checks finally sort by relevance.
