homepage Welcome to WebmasterWorld Guest from 54.205.228.154
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySql Index Problem using two tables
jeffsbaker




msg:4176888
 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

 

eelixduppy




msg:4178287
 1:46 am on Jul 29, 2010 (gmt 0)

Try using a foreign key. For example:

ALTER TABLE
users
ADD FOREIGN KEY
(comments_page)
REFERENCES comments(page)

jeffsbaker




msg:4178596
 2:58 pm on Jul 29, 2010 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved