Welcome to WebmasterWorld Guest from 54.196.153.46

Forum Moderators: open

Message Too Old, No Replies

MySql Index Problem using two tables

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

New User

5+ Year Member

joined:July 26, 2010
posts: 3
votes: 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 July 29, 2010 (gmt 0)

Senior Member

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

joined:Nov 12, 2005
posts:5966
votes: 0


Try using a foreign key. For example:

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

New User

5+ Year Member

joined:July 26, 2010
posts:3
votes: 0


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

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members