1

Have the MySQL SELECT query below that is awfully slow.

It takes ~1.0 seconds to execute despite having only 300K rows and indexes, so I would love to find a way to get it to execute faster since it's a query that needs to be run again and again.

The query:

SELECT p.id, p.image, c.name, s.name, MIN(p.saleprice)
FROM products p 
JOIN shops s ON p.shopid = s.id 
JOIN products_category pc ON p.id = pc.product_id 
JOIN categories c ON pc.category_id = c.id
WHERE p.brand_id > 0
AND pc.category_id = 46
AND pc.active = 1
AND p.price > 0
AND p.saleprice > 0
AND p.saleprice < p.price
AND (p.last_seen > DATE_SUB(NOW(), INTERVAL 2 DAY))
GROUP BY p.image

The query returns 960 rows.

The table products has 300.000 rows and these columns + one index:

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(512) DEFAULT NULL,
  `shopid` int(11) DEFAULT NULL,
  `image` varchar(512) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `saleprice` int(11) DEFAULT NULL,
  `last_seen` datetime DEFAULT NULL,
  `brand_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `brand_id etc` (`brand_id`,`shopid`,`last_seen`,`price`,`saleprice`)
) ENGINE=InnoDB AUTO_INCREMENT=391671 DEFAULT CHARSET=utf8

The table products_categories also has 300.000 rows and these columns + two indexes:

CREATE TABLE `products_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `active` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id etc` (`category_id`,`active`),
  KEY `product_id etc` (`product_id`,`active`)
) ENGINE=InnoDB AUTO_INCREMENT=373364 DEFAULT CHARSET=utf8

The shops table. It has 15 rows and these columns:

CREATE TABLE `shops` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `active` int(11) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8

Based on similar questions here, I have tried nesting things with an inner select:

SELECT p.id, p.image, c.name, s.name, MIN(p.saleprice)
FROM 
(SELECT * FROM products WHERE p.brand_id > 0 AND price > 0 AND saleprice > 0 AND saleprice < price AND (p.last_seen > DATE_SUB(NOW(), INTERVAL 3 DAY))) p 
JOIN shops s ON p.shopid = s.id 
JOIN products_category pc ON p.id = pc.product_id 
JOIN categories c ON pc.category_id = c.id 
WHERE pc.category_id = 46
AND pc.active = 1
GROUP BY p.image

It didn't help. The version with the inner select takes ~1,3 seconds to execute.

The problem seems to be the join between products and products_category, i.e. the two big tables with 300K rows each.

The GROUP BY p.image is necessary (some products are just different sizes, so we use p.image to filter these out).

Maybe there's a trick I can do with my indexes? Or can any of you spot something else I should optimize?

EXPLAIN of the query:

id  select_type table   partitions  type    possible_keys                   key             key_len ref             rows    filtered    Extra
1   SIMPLE      c       \N          const   PRIMARY                         PRIMARY         4       const           1       100.00      Using temporary; Using filesort
1   SIMPLE      pc      \N          ref     category_id etc,product_id etc  category_id etc 10      const,const     43104   100.00      Using where
1   SIMPLE      p       \N          eq_ref  PRIMARY,brand_id etc            PRIMARY         4       pc.product_id   1       5.00        Using where
1   SIMPLE      s       \N          eq_ref  PRIMARY                         PRIMARY         4       p.shopid        1       100.00      \N
  • please add full table definitions and output of explain for the query – Nikita Jul 9 at 18:34
  • 1) Add DDL for all tables. 2) Add EXPLAIN. 3) Add tablename aliases to ALL columns names used in the query. – Akina Jul 9 at 18:46
  • 1
    Good points. I have updated my question with table columns + explain. Aliases should be in the query. – Mads Jul 9 at 18:58
  • 1
    as we said we need to see CREATE TABLE, not just the list of columns with data types – Nikita Jul 9 at 19:10
  • 1
    CREATE TABLEs added. The group by is necessary (explanation added), but adding an index on p.image alone helped a bit. The query then takes 0.7 seconds to run. – Mads Jul 10 at 2:58
0

KEY brand_id etc (brand_id,shopid,last_seen,price,saleprice)

This index does not support this query well.

The first field in the index is brand_id, but you are only filtering by "> 0".
Since I doubt you have many brands with negative id's, this probably isn't going to help very much.

The next field in the index is shopid, which doesn't appear in your where clause at all.

Based on this cursory analysis (and MySQL will do a lot more than that), this index will probably be discounted and the table scanned serially instead.

A better discriminator for choosing the rows you want returned would be last_seen. Consider adding an index on that (or with that as its first field) and you should see some improvement.

| improve this answer | |
  • Great points. Thanks a lot. Best performance so far is a new index on image, last_seen => the query now takes ~0.3 seconds which is a big improvement to the original ~1.0 seconds. – Mads Jul 10 at 18:11

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.