How to improve this mysql query in speed .

Question

here it is

SELECT tbl_rls . * , (

SELECT count( * )
FROM `comments`
WHERE `post_id` = `tbl_rls`.`id`
) AS `t_comments`
FROM `tbl_rls`
WHERE 1 =1
AND `status` <> 'denied'
AND (
`id`
IN (

SELECT `rls_id`
FROM `tbl_visitors_logs`
WHERE `date` LIKE '2010-07-02%'
AND `page_type` = 'post'
GROUP BY `rls_id`
ORDER BY count( * ) DESC
)
)
AND (
`cat` = '6'
OR `cat`
IN (

SELECT `id`
FROM `tbl_cats`
WHERE `parent_id` = '6'
)
)
ORDER BY `tbl_rls`.`date` DESC
LIMIT 0 , 20

This is almost killing DB when executing , can some suggest solution to make it speedy ?

I am here to provide any additional info needed.

Thanks.

Answer 1

Don't use subqueries. Each subquery runs once for every row. So if the outer query returns 10 rows, then the inner query will be run 10 times.

The effect is made even worse by the fact that you have a subquery in a subquery. The effect multiplies, so if the outer one returns 10 rows and the inner one returns 10 rows, then the inner most one will run 100 times.

Edit: never mind on that last paragraph--it looked like you had a subquery within a subquery, but looking at it again, you don't. Either way, don't use subqueries.

Answer 2

Have you run an EXPLAIN command to see which part of the query is running slow?

Also, this line could be a problem: WHERE date LIKE '2010-07-02%' This could be causing the date column to be converted to a string (please tell me it's not a string!) which will prevent any index being used. Try WHERE DATE(date) = '2010-07-02' instead.

Answer 3

You can try this (without data it is hard to test)

SELECT r.*, COUNT(c.id) 
FROM tbl_rls r, comments c, tbl_visitors_logs v, tbl_cats t 
WHERE c.post_id = r.id 
    AND v.rls_id = r.id 
    AND t.parent_id = r.cat 
    AND r.status <> 'denied' 
    AND v.`date` LIKE '2010-07-02%' 
    AND page_type = 'post' 
    AND cat = 6 OR t.parent_id = 6 
GROUP BY c.post_id 
ORDER BY r.`date` DESC 
LIMIT 0, 20

Is this data structure correct?

CREATE TABLE IF NOT EXISTS `tbl_cats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `tbl_rls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` varchar(10) NOT NULL,
  `cat` int(11) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `tbl_visitors_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rls_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `page_type` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `commetn` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Answer 4

Here's my re-write of your query:

   SELECT t. *, 
          x.t_comments
     FROM tbl_rls t
LEFT JOIN (SELECT c.post_id,
                  COUNT(*) AS t_comments
             FROM COMMENTS c
         GROUP BY t.post_id) x ON x.post_id = t.id
     JOIN tbl_visitors_logs tvl ON tvl.rls_id = t.id
                               AND tvl.date LIKE '2010-07-02%'
                               AND tvl.page_type = 'post'
    WHERE t.status != 'denied'
      AND (t.cat = '6' OR t.cat IN (SELECT `id`
                                      FROM `tbl_cats`
                                     WHERE `parent_id` = '6'))
ORDER BY t.`date` DESC
   LIMIT 0, 20

Answer 5

SELECT tbl_rls.*, 
       COUNT(distinct comments.id) AS t_comments 
  FROM tbl_rls 
       JOIN tbl_visitors_logs tvl ON tvl.rls_id = tbl_rls.id
        AND tvl.page_type =  'post'
        AND DATE(tvl.date) = '2010-07-02'
       LEFT JOIN comments ON comments.post_id = tbl_rls.id
       LEFT JOIN tbl_cats ON tbl_cats.id =  cat AND tbl_cats.parent_id = '6'
 WHERE status <> 'denied' 
   AND (cat = 6 OR tbl_cats.id is not null)
 GROUP BY tbl_rls.id                           
 ORDER BY tbl_rls.date DESC 
 LIMIT 0, 20 

Answer 6

The best thing you could do is get rid of the LIKE keyword, and simply say:

WHERE v.Date > '2010-07-02' AND v.Date < '2010-07-03'

That way, you'll get everything for the day (or whatever date range you need to). The best way to think about it is that mySQL will have to go through and evaluate each row, even though they're already a datetime field. If the field v.Date is regularly searched, you could put an index on it to speed things up, and then it will make things quicker because it'll have an idea where the data is already.

You can also use COUNT(ID) instead of counting everything. Counting one field instead of 10 or 20 or 50 can save a few milliseconds.