MYSql Query Performance

I have following query where I am fetching Question count for exams created by CreatorID 1001

SELECT count(Questions.question_id) QuestionCount 
    FROM Exams 
        LEFT JOIN Questions  
            ON Exams.exam_id = Questions.exam_id_fk 
    WHERE Exams.CreatorId='1001'

I know this is really stupid question but at some point its possible that there can be 10,开发者_如何转开发000 users executing same query I just want to make sure about performace. So is there any othso er better way than this to do it ?

Thanks.

Provided you do not need the Exams where no questions have been defined, the following should be faster:

SELECT
  count(Questions.question_id) QuestionCount 
FROM Exams 
INNER JOIN Questions  
        ON Exams.exam_id = Questions.exam_id_fk 
WHERE Exams.CreatorId='1001'

Also make sure you have an index on the appropriate fields. Try using EXPLAIN on the query.

Any reason why your LEFT JOIN couldn't be an INNER JOIN instead?

Also, make sure you have indexes on:

• Exams.CreatorId
• Exams.exam_id
• Questions.exam_id_fk

It looks good to me. Just make sure that your join columns match and are indexes. I wouldn't preoptimize it.

If you do find that this query has a performance issue, by the context of the table and column names, you could store and update the number of questions on the exam. Assuming that adding and removing questions isn't occurring a lot.