Bring records that is not in another table

Asked

Viewed 58 times

1

SELECT * FROM quiz WHERE status='publicado' AND NOT quiz.id_quiz IN (SELECT id_quiz FROM quiz_resolvido WHERE quiz_resolvido.id_usuario=1) order by rand() LIMIT 0,10;

Today I use this Query to select only the quizzes that were not solved by the selected user 1.

It has been very slow because of many rows in the quiz_solved table. Has a better way to display the quizzes that were not solved by the selected user 1?

mysql sql database
  • 3

    Expose the structure of your tables (including keys) to evaluate how the database is performing the query

  • Read that

  • In this case you are left with few options, what you can do is create a mv for the quiz_solved table, so you can get faster.

2 answers

5


Create an index for your table:

ALTER TABLE quiz_resolvido ADD INDEX quiz_resolvido_index (id_usuario, id_quiz);

More information on the answer to the question Indexes in Mysql queries and What is the INDEX index in Mysql for?.

The response of Alex with respect to the NOT EXISTS should also improve performance a little, only maybe not enough.

1

 SELECT *
    FROM quiz a
   WHERE status = 'publicado'
     AND NOT EXISTS (SELECT 1
                       FROM quiz_resolvido b
                      WHERE a.id_quiz = b.id_quiz
                        AND b.id_usuario = 1)

  • Still very slow. There are more than 4 million records in solved.

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.