lampstax Posted August 6, 2008 Share Posted August 6, 2008 EXPLAIN SELECT `u`.*, count(`c`.`commentID`) AS `ccount` FROM `usr` AS `u` LEFT JOIN `comments` AS `c` ON `c`.`usrID` = `u`.`usrID` GROUP BY `u`.`usrID` Result is id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE u ALL NULL NULL NULL NULL 47122 Using temporary; Using filesort 1 SIMPLE c ref usrID usrID 4 alternet_staging.u.usrID 19 -------------------------------------- There's actually 600k+ rows in the comments table and about 50k rows in usr. If I remove the 'count' portion of it, it runs pretty quick, but is a pig otherwise. What am I doing wrong? Quote Link to comment Share on other sites More sharing options...
fenway Posted August 7, 2008 Share Posted August 7, 2008 Why are there no indexes on the usr table? Like on usrID? Quote Link to comment Share on other sites More sharing options...
lampstax Posted August 7, 2008 Author Share Posted August 7, 2008 Hi, Sorry for the lack of information Fenway, I just read the link in your signature. Table Usr [pre] Keyname Type Cardinality Field PRIMARY PRIMARY 47124 usrID email UNIQUE 47124 email url INDEX 5890 url password INDEX 47124 password username INDEX 47124 username [/pre] Table Comments [pre] Keyname Type Cardinality Field PRIMARY PRIMARY 630584 commentID storyID INDEX 18546 storyID parentID INDEX 315292 parentID usrID INDEX 33188 usrID [/pre] Both tables has index for usrID. Quote Link to comment Share on other sites More sharing options...
fenway Posted August 7, 2008 Share Posted August 7, 2008 OK, I see what you mean. First, why the LEFT JOIN? Second, if you try COUNT(*), is it any better? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.