I was wondering if someone could help me with a mysql indexing problem.
The index of 'username' for this code works fine:
EXPLAIN SELECT count( * ) AS num_messages
FROM messages, users
WHERE messages.username = 'johndoe'
AND users.username = 'johndoe'
AND messages.sent_date >= users.last_activity
Here is the explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE users ref username username 53 const 1 Using where
1 SIMPLE messages ref username username 53 const 195 Using where
But in the following table the index of 'page' which corresponds to the users.id is not working:
EXPLAIN SELECT count( * ) AS num_comments
FROM comments, users
WHERE users.username = 'johndoe'
AND comments.page = users.id
AND comments.sent_date
>= users.last_activity
Here is the explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE users ref PRIMARY,username username 53 const 1 Using where
1 SIMPLE comments ALL page NULL NULL NULL 52 Range checked for each record (index map: 0x2)
Does anyone know why MySql is not using the index key 'page' and how I can get it to use the index?