MYSql Query Performance

Question

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.

Jacco · Accepted Answer · 2011-08-09 21:16:57Z

3

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.

answered Aug 9, 2011 at 21:16

Jacco

23.9k18 gold badges92 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Stefanelli · Accepted Answer · 2011-08-09 21:17:46Z

1

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

answered Aug 9, 2011 at 21:17

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

2 Comments

James P. Over a year ago

What is the advantage between using a LEFT and INNER JOIN? I admit to using INNER all the time but can't justify it.

Jacco Over a year ago

INNER JOIN is faster, but omits the results where no record to join could be found.

Paul DelRe · Accepted Answer · 2011-08-09 21:18:08Z

0

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.

answered Aug 9, 2011 at 21:18

Paul DelRe

4,0392 gold badges26 silver badges27 bronze badges

Collectives™ on Stack Overflow

MYSql Query Performance

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related