How to improve this query

[quadrant6]

I have a simple query that is showing up in a log as sometimes taking a long time. The query is below (with explain in front of it). 'code' table has 40 thousand rows. The 'user' table has about 50 thousand rows.

What should I do either by altering the query or adding indexes to speed things up?

explain SELECT * FROM `user`,code WHERE `user`.user_id = code.user_id
AND code.code = '50816ef9621b4142760090e928d22658e79e0415d1cad824bdb43';

Output is

>> +----+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------------+
>> | id | select_type | table | type   | possible_keys | key     | key_len | ref                 | rows  | Extra       |
>> +----+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------------+
>> |  1 | SIMPLE      | code | ALL    | user_id       | NULL    | NULL    | NULL                | 35696 | Using where | 
>> |  1 | SIMPLE      | user  | eq_ref | PRIMARY       | PRIMARY | 4       | mydb.code.user_id |     1 |             | 
>> +----+-------------+-------+--------+---------------+---------+---------+----------------------+-------+-------------+
>> 2 rows in set (10.11 sec)

[Old Pedant]

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

[quadrant6]

In the long run, though, you will surely get the best performance by adding an index on the CODE.CODE column.

Thanks. So it should be a 'unique' index correct?

I also find that the code table often needs to be 'optimized' because it has a little red in the overhead column under phpmyadmin but is this just to be expected with a table that's being written to and rows removed frequently?

[Old Pedant]

Originally Posted by quadrant6

Thanks. So it should be a 'unique' index correct?

How can I know? Yes, if the code is supposed to be unique IN THE ENTIRE TABLE. No, if duplicates are allowed. That's up to you.

I also find that the code table often needs to be 'optimized' because it has a little red in the overhead column under phpmyadmin but is this just to be expected with a table that's being written to and rows removed frequently?

If you want to believe phpmyadmin, that's up to you.

Personally, I compact my tables about once a year or so, whether they need it or not. (And they usually don't.)

It's nearly impossible for a table that is being updated frequently to be "optimized" at all times. Most of the time, you should just let MySQL do its thing. It does a pretty good job at reusing space.