查询需要优化还是我只需要更高的最大连接数？

Question

I have a mysql database. My site has been getting a lot of max_user_connections errors, and since I can't increase the limit for a few more days, I was wondering if you guys could help me optimize this query that's taking between 1 and 4 seconds to complete. The 'status' table is InnoDB with 230,221 rows, and there are indexes already on it, but is it just a poorly written query?

SELECT status.id,users.id 
  FROM users, status 
 WHERE clan='someClan' 
   AND status.author!='loggedInUser' 
   AND status.anonymous!='someUser' 
   AND users.username='someUser' 
   AND status.data!='' 
   AND status.postdate > users.news_read 
GROUP BY postdate LIMIT 2

Thank you for any help.

Answer 1

You need to provide the proper connection between the users and the status table. Right now you are returning size(users table) * size(status table) number of rows.

From your comments, let me assume that you know the current user's users.id.

SELECT status.id
  FROM status
 WHERE status.clan='someClan'     \\ assuming clan is in status table
   AND status.author!='someUser' 
   AND status.anonymous!='someUser'  
   AND status.data!='' 
   AND status.postdate > (Select users.news_read 
                            from users
                           where users.username='someUser' 
                         )
GROUP BY postdate LIMIT 2