| Caching content for anonymous users [message #165641] |
Fri, 29 July 2011 10:46  |
eclipsewebmaster
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
|
Member |
|
|
I'm having problems with the "Unanswered messages" link, and other similar ones, when a user is not logged in.
The query used to generate some pages causes a tmp table on disk. In Eclipse's case, that tmp table is 1021M in size, and it is growing every month. The query takes about 4s to run. We have a 16G ramdisk, but there are some occasions where multiple users hitting similar links can cause our ramdisk to fill up.
Since some of the joined tables in those queries are constantly changing, MySQL's query cache cannot be used, so the query must be run constantly. Below is a sample of the query.
So my request is to either a) cache the results of this query in a table that gets updated every x minutes, or change the structure of the query so that it can benefit from the mysql query cache.
For the time being, I've had to disable those "Unanswered Messages" pages at Eclipse.
SELECT
m.*, COALESCE(m.flag_cc, u.flag_cc) AS disp_flag_cc, COALESCE(m.flag_country, u.flag_country) AS disp_flag_country,t.thread_opt, t.root_msg_id, t.last_post_id, t.forum_id,f.message_threshold, f.name, u.id AS user_id, u.alias AS login, u.avatar_loc, u.email, u.posted_msg_count, u.join_date,u.location,u.sig, u.custom_status, u.icq, u.jabber, u.affero, u.aim, u.msnm, u.yahoo, u.skype, u.google, u.twitter, u.last_visit AS time_sec, u.users_opt,l.name AS level_name, l.level_opt, l.img AS level_img,p.max_votes, p.expiry_date, p.creation_date, p.name AS poll_name, p.total_votes,pot.id AS cant_vote,r.last_view, mm.id AS md,m2.subject AS thr_subject,g1.group_cache_opt
FROM fud_msg m
INNER JOIN fud_thread t ON m.thread_id=t.id
INNER JOIN fud_msg m2 ON m2.id=t.root_msg_id
INNER JOIN fud_forum f ON t.forum_id=f.id
INNER JOIN fud_cat c ON f.cat_id=c.id INNER JOIN fud_group_cache g1 ON g1.user_id=0 AND g1.resource_id=f.id
LEFT JOIN fud_read r ON r.thread_id=t.id AND r.user_id=0
LEFT JOIN fud_users u ON m.poster_id=u.id
LEFT JOIN fud_level l ON u.level_id=l.id LEFT JOIN fud_poll p ON m.poll_id=p.id LEFT JOIN fud_poll_opt_track pot ON pot.poll_id=p.id AND pot.user_id=0
LEFT JOIN fud_mod mm ON mm.forum_id=f.id AND mm.user_id=0
WHERE m.apr=1
AND t.replies=0
AND (mm.id IS NOT NULL OR ((g1.group_cache_opt) & 2) > 0)
ORDER BY
f.last_post_id DESC , t.last_post_date DESC , m.post_stamp DESC LIMIT 40 OFFSET 0;
|
|
|
|
|
|
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]