Two left joins and one query to MySQL performance problem

Question

Hello,

I'm designing a project for quizzes and quizz results. So I have two tables: quizz_result and quizz. quizz has primary key on ID and quizz_result has foreign key QUIZZ_ID to quizz identity.

Query below is designed to take public quizzes ordered by date with asociated informations: if current user (683735) took this quizz and has a valid result (>0) and how many people filled this quizz till this point in time.

So i did this simple query with two left joins:

select     
  a.*,  
  COUNT(countt.QUIZZ_ID) SUMFILL
from 
  quizz a
  left join quizz_result countt
    on countt.QUIZZ_ID = a.ID 
group by 
   a.ID

And added indexes on these columns: Quizz:

ID, (ID, DATE), PUBLIC, (PUBLIC, DATE)

And on quizz_result:

ID, (QUIZZ_ID, USER_ID), QUIZZ_ID, USER_ID, (QUIZZ_ID, QUIZZ_RESULT_ID)

But still when I do query it takes like about one minute. And i have only 34k rows in QUIZZ_RESULTS and 120 rows in QUIZZ table.

When I do EXPLAIN on this query I get this:

SELECT TYPE: simple, possible keys:  IDX_PUBLIC,DATE, rows: 34  extra: Using where; Using temporary; Using filesort
SELECT TYPE: simple, possible keys: IDX_QUIZZ_USER,IDX_QUIZZ_RES_RES_QUIZ,IDX_USERID,I..., rows: 1, extra: nothing here
SELECT TYPE: simple, possible keys: IDX_QUIZZ_USER,IDX_QUIZ_RES_RES_QUIZZ,ID_RESULT_ID, rows: 752, extra:  Using index

And I don't know what to do to optimise this query. I see this:

Using where; Using temporary; Using filesort

But still I don't know how to get this better, or maybe number of rows in last select is to hight? 752?

How can I optimise this query?

EDIT: I've upadated query to this one with only one left join because it has the same long execution time.

EDIT2: I did remove everything to and thats it: this simple select with one query takes 1s to execute. How to optimise it?

Answer 1

What about an index on (USER_ID, QUIZZ_ID, QUIZZ_RESULT_ID), since they're all AND'd together?

Answer 2

Try taking some of those additional conditions out of your joins. Moving them to the where clause can sometimes help. Also, consider putting the core joins into their own subquery and then limiting that with a where clause.

Answer 3

I've changed it to this:

select     
  a.*,  
  COUNT(a.ID) SUMFILL
from 
  quizz a
  left join quizz_result countt
    on countt.QUIZZ_ID = a.ID 
group by 
   a.ID

And it's good now.

Answer 4

Try this:

SELECT  q.*,
        (
        SELECT  COUNT(*)
        FROM    quizz_results qr
        WHERE   qr.quizz_id = q.id
        ) AS total_played,
        (
        SELECT  result
        FROM    qr.quizz_id = q.id
                AND user_id = 683735
        ) AS current_user_won
FROM    quizz q