Query slow in PHP but fast in client

Question

Me and our team are experiencing a strange problem with MySQL queries. We use a SELECT statement with a COUNT and for some reason it's quite 'fast' in the client we use (SQLyog), but it's realy slow when we use PHP.

We have tried to use the ancient mysql_query(), the mysqli extension and we also tried to use PDO but all didn't make a difference.

On other posts here on Stackoverflow we found that it might be a DNS issue and that it could be fixed using 'skip_name_resolve' in the my.ini but we already had this in our configuration.

Timed results:

Client: 2.092 sec PHP: 9.1071 sec

This is the query we use:

SELECT SQL_NO_CACHE
  COUNT(m.mm_id) AS total
FROM
  db.media_multimedia m
WHERE m.cat_id IN
  (SELECT
    mc.cat_id
  FROM
    db.media_multimedia_category mc
  WHERE mc.cat_active = 1)
  AND m.mm_published = 1
  AND (
    m.mm_title LIKE "%denniy%"
    OR m.mm_text LIKE "%denniy%"
    OR m.mm_id IN
    (SELECT
      a.mm_id
    FROM
      db.`media_tag_multimedia` a
      LEFT JOIN media.`media_tag` b
        ON a.`tag_id` = b.tag_id
    WHERE b.tag_name LIKE "%denniy%")
  )
  AND m.mm_publishing_date >= "2012-04-24 00:00:00"
  AND m.mm_publishing_date <= "2013-04-24 23:59:59" ;

*NOTE: for this testcase we added SQL_NO_CACHE to the query to make sure we always fetch a new result set.*

We are using the following PHP and MYSQL versions:

MySQL: 5.1.61 PHP: 5.3.3

Any suggestions to fix this problem?

Answer 1

I can't explain the difference in performance, although if I had to guess I'd say one or some of the following things are in play:

• The server is optimising your query differently because of the different client connections,
• The different location of your SQLyog and PHP clients might be a factor

Like I said, just guesses.

But regardless, I've attempted to tidy up your query as follows. I wonder if this might perform better (and more consistently)?

SELECT SQL_NO_CACHE
  COUNT(m.mm_id) AS total

FROM
  db.media_multimedia m

  INNER JOIN db.media_multimedia_category mc
  ON m.cat_id = mc.cat_id
  AND mc.cat_active = 1

  LEFT JOIN db.media_tag_multimedia a
  ON m.mm_id = a.mm_id

  INNER JOIN media.media_tag b
  ON a.tag_id = b.tag_id

WHERE 
  m.mm_published = 1
  AND 
  (
    m.mm_title LIKE "%denniy%"
    OR 
    m.mm_text LIKE "%denniy%"
    OR 
    b.tag_name LIKE "%denniy%"
  )
  AND m.mm_publishing_date >= "2012-04-24 00:00:00"
  AND m.mm_publishing_date <= "2013-04-24 23:59:59" ;