Geek asks and answers

The request is slow in PHP, but a fast client

My team and I have a strange problem with MySQL queries. We use the SELECTc operator COUNTand for some reason is pretty “fast” in the client used (SQLyog), but it is very slow when we use PHP.

We tried to use ancient mysql_query (), the mysqli extension, and we also tried to use PDO, but all of this did not help.

In other posts here at Stackoverflow, we found that this could be a DNS problem, and that this could be fixed using "skip_name_resolve" in my.ini, but we already had this in our configuration.

Temporary results:

Client: 2,092 with PHP: 9.1071 sec

This is the request 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" ;

* . SQL_NO_CACHE , , . *

PHP MYSQL:

MySQL: 5.1.61 PHP: 5.3.3

?

+5
1

, , , , :

  • - - ,
  • SQLyog PHP

, .

, , . , ( )?

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" ;
+2
source

More articles:


All Articles