CodingForums.com - View Single Post

Old Pedant · 11-16-2012, 08:20 PM

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:

Code:

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 column.

11-16-2012, 08:20 PM	PM User \| #2
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,168 Thanks: 59 Thanked 3,993 Times in 3,962 Posts	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: Code: 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 column. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.