too many queries...

[acidglitter]

my site has been down or incredibly slow most of this week. i've been talking to my server and now its starting to look like the problem is there are too many mysql queries and they're crashing the site... i have no idea where to start to fix this. does anyone have any ideas?

[fenway]

Yup... the slow query log.

[acidglitter]

i googled that but i'm not sure how to find that with phpMyAdmin

[dotservant.com]

Your mysql server admin will have to enable the 'slow query log' option in my.cnf and mysql will start logging    slow queries to a log file, server admin will then be able to extract out the queries related to your db.

 

 

reference :-

 

http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

[acidglitter]

right now its looking like its not possible to enable that on my server without changing too many other things. i'm really stressing out about this. does anyone know anything else i could do?

[fenway]

I don't know what "other things" you are talking about ....

 

Are you in control of all of the queries being run on the DB? If so, run EXPLAINs on the frequent ones.

[acidglitter]

I don't know what "other things" you are talking about ....

 

this is just what my server said:

"Enabling this log would be a global change at the root of the server and you won't have access to the log file."

 

 

the one on the home page is like this

SELECT *,
	DATEDIFF(DATE(vip), CURDATE()) AS 'days_left'
FROM members
WHERE DATEDIFF(DATE(vip), CURDATE())>0
	AND friendid NOT IN ($featuredspaces)
ORDER BY DATEDIFF(DATE(vip), CURDATE()) DESC

 

and with explain it said..

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE members ALL NULL NULL NULL NULL 79011 Using where; Using temporary; Using filesort

 

 

 

and on the add page

SELECT friendid, name, picture, points,
DATEDIFF(DATE(vip), CURDATE()) as 'vip' FROM members
WHERE friendid NOT IN
(SELECT theirs FROM added WHERE yours='{$_SESSION['friendid']}')
AND friendid != '{$_SESSION['friendid']}'
ORDER BY (DATEDIFF(DATE(vip), CURDATE())>0) DESC, points DESC, lastlogin DESC LIMIT $limit

 

and the explain for that

 

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY members ALL NULL NULL NULL NULL 79013 Using where; Using temporary; Using filesort

2 DEPENDENT SUBQUERY added ALL NULL NULL NULL NULL 3216 Using where

 

[aschk]

Wow, nice queries, have you got any indexes on your tables? Because it seems that none are being used. Also a "blah IN (...)" is not particularly efficient when the (...) is a SELECT *, thus I recommend you change this to a LEFT JOIN and add indexes on the adjoining columns.