Hey, I have this query:
SELECT team. * , COUNT(*) - IF(team_id IS NULL, 1, 0) AS numberofmember FROM team LEFT JOIN teammember ON ( team.id = teammember.team_id ) WHERE CONCAT( team.name, team.description ) LIKE '% %' AND team.status =1 AND team.inviteonly =0 GROUP BY team.id ORDER BY numberofmember DESC This works fine, but it feels like it is not optimal. I have index on team.id and teammember.team_id, so that is ok I believe. But it feels I should do the count thing separately. However, I can not do that because my system does not support sub queries. Would it be better to first just list all team and then make a new query that counts the number of members? And if you ask why I am doing the " COUNT(*) - IF(team_id IS NULL, 1, 0) " it is because I want to count the number of members, but if there are no members the row count will still be 1 but with NULL in the teammember fields. So if there is NULL there I know there are no members, so I need to subtract 1 from them so the result is 0. Maybe that is also slowing down the query. I have to point out that right now I do not have any performance issue, this is just theory that I am thinking about, I want to build a system that can be robust and not needs to be redeveloped. Best regards, Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
