3

I have 2 tables, products and stores, and a pivot table in the middle called product_store.

Here is the relevant portion of the table structures (from MySQL):

products

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`global` tinyint(1) NOT NULL DEFAULT '0',

stores

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,

product_store

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned NOT NULL,
`store_id` int(10) unsigned NOT NULL,
`show` tinyint(1) NOT NULL,

The logic of the global vs show flags are as follows:

  • If the global flag is 1, then assume this product is on every store, except when there is a product_store entry with show == 0.
  • If the global flag is 0, then assume this product is only on stores where there is a product_store entry with show == 1.

In other words, global == 1 means show this product everywhere, show == 1 means show the product in this store, show == 0 means do not show the product in this store, and show takes precedence over global.

What I want to be able to do is fetch products based on a known store_id, where:

  • products.global == 1 AND there is NO product_store entry for the product_id, store_id
  • OR
  • there is a product_store entry for the product_id, store_id AND product_store.show == 1

Currently I have this query, with a GROUP BY and a nested SELECT but it pulls many records from the database which are then consolidated by the GROUP BY and it's quite slow. I am thinking that there must be something that's faster, without the nested select.

SELECT products.id, products.name, products.global, product_store.show, product_store.store_id
    FROM products
    LEFT JOIN product_store ON products.id=product_store.product_id
    WHERE ((products.global=1 AND
          ((SELECT COUNT(*) FROM product_store WHERE product_store.store_id=226 AND product_store.product_id=products.id) = 0)
         )
        OR (product_store.store_id=226 AND product_store.show=1)
        )
    GROUP BY products.id
    ;

226 is the store ID in this case, I only need to run this query for one store at a time so this can be considered as a constant.

Note that if I do the above query with the GROUP BY then I'm getting about 14 records which is correct (when I do the count myself). The system has a few thousand products and a few hundred stores so that product_store join table is rather large. If I do the above query without the GROUP BY then I'm getting ~ 1500 records which appears to be the list of all product/product store combinations that fit the LEFT JOIN clause.

Just to re-iterate, the above query works fine (although in MySQL versions > 5.7.5 there will be issues with the GROUP BY) but it appears to be inefficient and I am trying to figure out something better and faster.

SOME FURTHER DETAILS ON TABLE CONTENTS

Here are some quick queries on the relevant products and stores, etc. First to show ALL relevant matching products -- there are 15, all of which have global=1.

SELECT products.id, MD5(products.name), products.global
    FROM products
    -- .. omitted some classification information here
    ;

+-----+----------------------------------+--------+
| id  | md5(products.name)               | global |
+-----+----------------------------------+--------+
| 555 | 56e597c43cff47af57f006b7de7ff552 |      1 |
| 591 | 7ba5549546ce20e1dee02c7f6454b16f |      1 |
| 558 | 7b66d8dce0c112b3966d1940de44f9ad |      1 |
| 556 | bd3d39b9f5194184ea0853c70d7faa07 |      1 |
| 590 | ef6eccbd3795fbe70890220c09ed880d |      1 |
| 559 | b763e2b9ba305538b497da97fba066ca |      1 |
| 593 | e26744d617ed12ebffea4b996fecbbef |      1 |
| 594 | 06ba8ab6649c83577db053e4a89c1655 |      1 |
| 596 | 79f9241643e84e2a27bfc0b260432e82 |      1 |
| 595 | 85fe772dc61d27292c09a7604cf76dd7 |      1 |
| 597 | fd6c21db00e1b679c0d0d2ef8f3f6e00 |      1 |
| 560 | c8056818e3ed5885188d78a9440fa77c |      1 |
| 561 | 33d79c194e39e8e84cf743eefcd909df |      1 |
| 562 | 1c74d85823e509ab4b09e82314b09604 |      1 |
| 557 | b6a8027035b63bd4d4bd21169434426d |      1 |
+-----+----------------------------------+--------+

Now to look at the join with the product_store table. There is only one entry there for store_id 226, and it has show=0.

SELECT products.id, MD5(products.name), products.global, product_store.show
    FROM products
    INNER JOIN product_store ON products.id=product_store.product_id
    -- omitted the classification information as per the above
    WHERE product_store.store_id=226
    ;

+-----+----------------------------------+--------+------+
| id  | MD5(products.name)               | global | show |
+-----+----------------------------------+--------+------+
| 555 | 56e597c43cff47af57f006b7de7ff552 |      1 |    0 |
+-----+----------------------------------+--------+------+

... I therefore need to be able to construct a query that selects all 15 products where global=1 EXCEPT the product where product_store.show=0, therefore I need the set of 14 remaining products.

Would a UNION select work somehow? Or some form of intersect?

Sample: http://sqlfiddle.com/#!9/1342cf/5

2

Based on your sample, this seems to be an equivalent query:

SELECT products.id, products.name, products.global, product_store.show, product_store.store_id
FROM products
LEFT JOIN product_store
  ON products.id=product_store.product_id
 AND product_store.store_id=226
WHERE -- there is NO entry in product_store AND products.global=1
      (products.global=1 AND product_store.show IS NULL)
      -- there is an entry in product_store with show=1 
   OR (products.global=0 AND product_store.show = 1)

DISTINCT should not be neccessary.

I added this to your fiddle

Placing conditions in ON vs. WHERE is crucial for outer joins. In ON it's just a condition which never filters rows but might create NULLs in the inner table. WHERE is applied after the join, and here it filters (when applied on a column of the inner table all NULLs created by the outer join will be removed again, unless you add OR col IS NULL).

  • OK that worked with one minor variation - I had to put braces () around (products.id=product_store.product_id AND product_store.store_id=226). However, thanks, that appears to be the answer. – delatbabel Jul 27 '17 at 9:20

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.