Wednesday, July 25, 2012

Mysql function to check a value is numeric or not

0 comments
In Mysql 5.1 I need a function which would return true if a column value was Numeric and false if it was not.
I searched the internet and found the link -
http://stackoverflow.com/questions/2756736/how-to-only-select-numeric-data-from-mysql

It gave me a good idea about how to do it. Then I wrote my one as follows :-

CREATE FUNCTION is_numeric (sIn text) RETURNS tinyint
DETERMINISTIC
NO SQL
   RETURN sIn REGEXP '^-*[[:digit:]]+(\.[[:digit:]]+)?$' ;
 
Description -   



  • ^ = match at the begining of the string
  • $ = match at the end of the string
  • [:digit:] = digit class
  • ^[[:digit:]]+$ = from the begining to the end only digit characters
  • -* = 0 or more occurrances of - (minus sign)
  • (\.[[:digit:]]+)? = if a .(dot) is used then there must be 1 or more digit after that and by using (..)?, we ensured 0 or more occurrences of the item




  
Continue reading →
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.

Continue reading →

Labels