Help optimizing query ..

[lampstax]

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?

[fenway]

Why are there no indexes on the usr table? Like on usrID?

[lampstax]

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.

[fenway]

OK, I see what you mean.

 

First, why the LEFT JOIN?

 

Second, if you try COUNT(*), is it any better?