CustomPCSoftware.com

Hi, All!

Recently I faced a MySQL replication lag time problem. From time to time it increased dramatically (up to 200 sec’s) on new boxes (there are 4 boxes in cluster beyond load balancer) with 4Gb of RAM and really-really nice CPUs. I had to enable slow queries logging to figure out what was causing the problem.
Okay, the slowest query was…. yeah, in search subsystem:

Count: 5 Time=0.80s (4s) Lock=0.00s (0s) Rows=37.2 (186), xxxxx@localhost
SELECT a.title AS title,
a.created AS created,
CONCAT(a.introtext, a.fulltext) AS text,
u.title AS section,
CONCAT( ‘S’, a.id ) AS href,
‘S’ AS browsernav,
‘S’ AS type
FROM jos_content AS a
INNER JOIN jos_categories AS b ON b.id=a.catid
INNER JOIN jos_sections AS u ON u.id = a.sectionid
WHERE ( (LOWER(a.title) LIKE ‘S’ OR LOWER(a.introtext) LIKE ‘S’ OR LOWER(a.fulltext) LIKE ‘S’ OR LOWER(a.metakey) LIKE ‘S’ OR LOWER(a.metadesc) LIKE ‘S’) )
AND a.state = N
AND u.published = N
AND b.published = N
AND a.access <= N
AND b.access <= N
AND u.access <= N
AND ( a.publish_up = 'S' OR a.publish_up <= 'S' )
AND ( a.publish_down = 'S' OR a.publish_down >= ‘S’ )
GROUP BY a.id
ORDER BY a.created DESC
LIMIT N, N

Does anybody have idea why do they use LOWER? MySQL states “By default, MySQL searches are not case sensitive”. So, why add additional load to server?

Anyway, after removing “LOWER” the queries became about 40% faster.

Hope this will help anybody else.

Thanks.

Share This

This entry was posted on Thursday, April 19th, 2007 at 11:32 am and is filed under CMS, Joomla, MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.