As you can see, it says that it uses *NO* key when accessing the CODE table.
It is scanning *ALL* rows of the table.
Presumably, MySQL has decided that using the user_id
key would not help performance. *PROBABLY* MySQL is wrong in this case, and you probably could use STRAIGHT_JOIN to force it to use the index:
SELECT * FROM `user` STRAIGHT_JOIN code
ON `user`.user_id = code.user_id
WHERE code.code = '50816ef9621b4142760090e928d22658e79e0415d1cad824bdb43';
(Though shame on you for using SELECT * instead of selecting only the needed fields.)
In the long run, though, you will surely get the best performance by adding an index on the CODE.CODE