mysql where is null vs not exist-CodePudding

im trying to optimize mysql queries and i was wondering what is more efficient way to select rows by varchar(36) id that dont exist in a another table, which one is faster and is there a better way to do it that is not mentioned here? i can change the type from varchar(36) to bigint if needed

SELECT * FROM tasks
    WHERE (SELECT uuid FROM submitedTasks
               WHERE uuid = tasks.uuid) IS NULL;

SELECT * FROM tasks
    WHERE NOT EXISTS (SELECT uuid FROM submitedTasks
                WHERE submitedTasks.uuid = tasks.uuid)

CodePudding user response：

Those two queries will be almost exactly the same in terms of efficiency. If you are looking for an actual optimization you may want to use the LEFT JOIN / IS NULL solution.

According to https://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

(...) The code that executes EXISTS predicate is about 30% less efficient than those that execute index_subquery and LEFT JOIN optimized to use the Not exists method.

In your case it would be:

SELECT * FROM tasks
LEFT JOIN submitedTasks ON submitedTasks.uuid = tasks.uuid 
WHERE submitedTasks.uuid IS NULL;

Note - Make sure that you have index on submitedTasks.uuid column.