Tuesday, July 24, 2012

Mysql Query tune for a wordpress site

0 comments


Yesterday one of the developers of my team came to me with the following query which would summarize user ratings in the homepage of our wordpress site.
The query was taking 22 sec and it was not tolerable for a home page to take 22 sec every time the page load.

The basic query was as follows -

SELECT
userids.user_id,
u.user_login,
metaFirst.meta_value AS first_name,
metaPrimarySchool.meta_value AS primary_school_blog_id,
b.alias AS primary_school_blog_alias,
SUM(cp.measures) AS measures
FROM users AS u
INNER JOIN (
SELECT DISTINCT user_id FROM usermeta WHERE meta_key LIKE '%_capabilities'
AND
(
meta_value LIKE '%abc%'
)
) AS userids ON (u.ID = userids.user_id AND u.ID<>1)
INNER JOIN usermeta metaFirst ON (metaFirst.user_id=userids.user_id AND metaFirst.meta_key = 'first_name')
INNER JOIN usermeta metaPrimarySchool ON (metaPrimarySchool.user_id=userids.user_id AND metaPrimarySchool.meta_key = 'primary_school_blog_id')
INNER JOIN blogs b ON (b.blog_id = metaPrimarySchool.meta_value)
INNER JOIN cp cp ON (cp.uid=userids.user_id AND cp.timestamp>=1341100800)
GROUP BY cp.uid
ORDER BY measures DESC
limit 5;

We had to tune the query.

We took the following steps :-

Created a index on timestamp column of table cp.
create index cp_timestamp on cp(timestamp);

Created a 2 GB hot_cache and cached the most frequently used tables such as blogs and usermeta.

set global hot_cache.key_buffer_size=2197815296;
cache index blogs, usermeta in hot_cache;

Rewrite the query by replacing the derived join with EXISTS as we don't need any column from the derived "userids" in SELECT clause.



SELECT
cp.uid userid,
u.user_login,
metaFirst.meta_value AS first_name,
metaPrimarySchool.meta_value AS primary_school_blog_id,
b.alias AS primary_school_blog_alias,
b.blog_name AS blog_name,
SUM(cp.measures) AS measures
FROM users AS u
INNER JOIN usermeta metaFirst ON (metaFirst.user_id=u.id AND metaFirst.meta_key = 'first_name')
INNER JOIN usermeta metaPrimarySchool ON (metaPrimarySchool.user_id=u.id AND metaPrimarySchool.meta_key = 'primary_school_blog_id')
INNER JOIN blogs b ON (b.blog_id = metaPrimarySchool.meta_value)
INNER JOIN cp cp ON (cp.uid=u.id AND cp.timestamp>=cast(unix_timestamp(concat(year(curdate()),'-', lpad(month(curdate()),2,0),'-', '01')) as unsigned))
WHERE EXISTS
(
SELECT 1
FROM usermeta as userids
where meta_key LIKE '%_capabilities'
AND
(
meta_value LIKE '%abc%'
)
AND userids.user_id = u.id
)
GROUP BY cp.uid
ORDER BY measures DESC
LIMIT 100;



Now the query is giving result under a second.

Leave a Reply

Labels