0

I have a classifieds website and want to optimize the most intensive query in my server. I don´t have so much experience with MySQL (or any other DB).

This is the complete listing ads:

SELECT
    `category`.`id` AS `category_id`,
    `category`.`subcat` AS `category_name`,
    `category`.`desc` AS `category_desc`,
    `category`.`name` AS `category_pretty_name`,
    `ads`.`id`,
    `ads`.`header`,
    `ads`.`price`,
    `ads`.`oldprice`,
    `ads`.`sellfast`,
    `ads`.`foto1`,
    `ads`.`subcat_id`,
    `ads`.`updated`,
    `ads`.`created`,
    `ads`.`name`,
    `ads`.`phone`,
    `ads`.`hits`,
    `rating_ad`.`num_rates` AS `num_rates`,
    `rating_ad`.`average_rating` AS `average_rating`,
    SUBSTR(ads.body, 1, 160) AS body,
    `ads`.`user_id`,
    `ads`.`hash`,
    `ads`.`hidden`,
    `ads`.`email`,
    `promo`.`promotype` AS `promo_type`,
    `promo`.`end_promo`,
    `supercategory`.`name` AS `supercategory_name`,
    `supercategory`.`id` AS `supercategory_id`
FROM
    `ads`
JOIN `category` ON `category`.`id` = `ads`.`subcat_id`
JOIN `supercategory` ON `supercategory`.`id` = `category`.`cat`
LEFT OUTER JOIN `promo` ON `promo`.`ads_id` = `ads`.`id`
LEFT OUTER JOIN (
    SELECT
        ad_id,
        TRUNCATE (AVG(rating), 2) AS average_rating,
        COUNT(user_id) AS num_rates
    FROM
        ads_rating
    GROUP BY
        ad_id
    HAVING
        COUNT(user_id) >= 1
) AS rating_ad ON `rating_ad`.`ad_id` = `ads`.`id`
WHERE
    `ads`.`sellfast` = '1'
AND `ads`.`subcat_id` = '19'
AND `recycle_bin` = 0
AND `hidden` = 0
ORDER BY
    `promo_type` DESC,
    `updated` DESC
LIMIT 100;

When I do the EXPLAIN Query, this is the result:

MySQL EXPLIN Results

So, I see in the Slow queries logs this is consuming about 0.350 secs. Is this optimizable?

This is the SHOW CRETE TABLE:

CREATE TABLE `ads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subcat_id` mediumint(2) NOT NULL,
  `province_id` mediumint(2) NOT NULL,
  `price` float(12,2) NOT NULL,
  `oldprice` float(12,2) NOT NULL,
  `type` enum('1','2','3') NOT NULL,
  `header` varchar(200) CHARACTER SET latin1 NOT NULL,
  `body` text CHARACTER SET latin1 NOT NULL,
  `email` varchar(150) CHARACTER SET latin1 NOT NULL,
  `public_email` varchar(150) NOT NULL,
  `name` varchar(50) CHARACTER SET latin1 NOT NULL,
  `phone` varchar(30) CHARACTER SET latin1 NOT NULL,
  `user_id` int(6) NOT NULL,
  `created` datetime NOT NULL,
  `foto1` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `foto2` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `foto3` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `foto4` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `foto5` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `updated` datetime NOT NULL,
  `hash` varchar(32) CHARACTER SET latin1 NOT NULL,
  `hits` int(6) NOT NULL,
  `new` tinyint(1) DEFAULT 0,
  `sellfast` tinyint(1) DEFAULT 0,
  `website` varchar(250) CHARACTER SET latin1 DEFAULT NULL,
  `tags` varchar(250) DEFAULT NULL,
  `brand_id` smallint(2) NOT NULL,
  `hidden` smallint(1) NOT NULL,
  `recycle_bin` smallint(1) NOT NULL,
  PRIMARY KEY (`id`,`hash`),
  KEY `header` (`header`) USING BTREE,
  KEY `tags` (`tags`) USING BTREE,
  KEY `phone` (`phone`) USING BTREE,
  KEY ` subcat_id` (`subcat_id`) USING BTREE,
  KEY `province_id` (`province_id`) USING BTREE,
  KEY `hidden` (`hidden`) USING BTREE,
  KEY `recycle_bin` (`recycle_bin`) USING BTREE,
  KEY `price` (`price`) USING BTREE,
  KEY `email` (`email`) USING BTREE,
  KEY `created` (`created`) USING BTREE,
  KEY `updated` (`updated`) USING BTREE,
  KEY `user_id` (`user_id`),
  KEY `new` (`new`),
  KEY `sellfast` (`sellfast`)
) ENGINE=MyISAM AUTO_INCREMENT=839759 DEFAULT CHARSET=utf8

Which task most do to made it?

1

Please provide SHOW CREATE TABLE; it is important for optimization to know, for example, which table recycle_bin is in.

Even before looking at the joins, get rid of the "index merge intersect" by building a "composite" index:

 INDEX(sellfast, subcat_id, recycle_bin, hidden)  -- in any order

A LEFT OUTER JOIN (aka LEFT JOIN), per se, is not the villain. Having a subquery in it may be the real villain. Several things can be done with such, but I am not sure any cure applies to your case:

  • Get rid of LEFT. But this would lose some rows if they are missing from ads_rating. Without LEFT, the subquery would probably be performed first instead of repeatedly. (If this is valid, try it first.)
  • Move the subquery into the SELECT. But there are two columns being provided, so it is unlikely to be practical.
  • Use WITH. But this requires version 8.0. What version are you using?
  • CREATE TEMPORARY TABLE for the subquery, then use it in place of the subquery. (This may be the best choice.)
  • Great! I´m using MariaDB 10.2, SO how do I create the composite INDEX? INDEX(sellfast, subcat_id, recycle_bin, hidden) – Erich García Jun 27 '18 at 0:40
  • ALTER TABLE ads ADD INDEX(sellfast, subcat_id, recycle_bin, hidden); – Rick James Jun 27 '18 at 3:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.