Welcome to WebmasterWorld Guest from 54.166.33.25

Forum Moderators: open

MySql Index Problem using two tables

   
8:32 pm on Jul 26, 2010 (gmt 0)



I was wondering if someone could help me with a mysql indexing problem when using two tables.
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 select statement the index of 'comments.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?
Thanks
1:46 am on Jul 29, 2010 (gmt 0)

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 5+ Year Member



Try using a foreign key. For example:

ALTER TABLE
users
ADD FOREIGN KEY
(comments_page)
REFERENCES comments(page)
2:58 pm on Jul 29, 2010 (gmt 0)



Thanks, but the foreign key does not work because the database is using MYISAM tables.
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month