Description: ------------ In a app I'm making I join prefix_groups and prefix_groupusers LEFT JOIN prefix_groups ON prefix_groups.group_id = prefix_groupusers.group_id There are around 200 groups, and about 3500 users which are all in some of those 200 groups. When doing this JOIN things get very very heavy for the database (mysql in this case) but with out this this takes no time, and makes alot of sleepy proccess for mysql. I tested adding some indexes in hope for a faster query but didn't do anything ;/

Also seems to be slow with big prefix_perm_users (3600 records). Doubt that it's the server, because everything else is blazing fast, and also added indexs to perm_users and that didn't help. I'm going to analyze this better, would be great if some one would try to emulate this. LEFT JOIN user_perm_users ON user_perm_users.auth_user_id = User.user_id this is the line in my sql that makes it all slow, hope ya get the point of this, if ya want to reproduce this and report back, so we know if it's the server or the database schema design :)

the issue iis with the prefix_groupusers, as soon as I set perm_user_id in it's own index, things get fast, as soon as I remove it again, things get slow again. So here's what we can do, stop having group_id and perm_user_id as primary key and move perm_user_id in it's own index. Any comments from other developers ?

Yeah, we have been misung primary keys on several relationship tables where adding an index per column and adding a unique contraint across all columns is probably the way to go. I fixed this in CVS.