Eu tenho 2 tabelas, productse stores, e uma tabela dinâmica no meio chamada product_store.
Aqui está a parte relevante das estruturas da tabela (do MySQL):
produtos
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`global` tinyint(1) NOT NULL DEFAULT '0',
lojas
`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,
A lógica dos sinalizadores globalvs é a seguinte:show
- Se o
globalsinalizador for 1, suponha que este produto esteja em todas as lojas, exceto quando houver umaproduct_storeentrada comshow== 0. - Se o
globalsinalizador for 0, suponha que este produto esteja apenas em lojas onde há umaproduct_storeentrada comshow== 1.
Em outras palavras, global== 1 significa mostrar este produto em todos os lugares, show== 1 significa mostrar o produto nesta loja, show== 0 significa não mostrar o produto nesta loja e showtem precedência sobre global.
O que eu quero fazer é buscar produtos com base em um store_id conhecido, onde:
- products.global == 1 E NÃO há entrada product_store para product_id, store_id
- OU
- há uma entrada product_store para product_id, store_id E product_store.show == 1
Atualmente eu tenho essa consulta, com um GROUP BYe um aninhado SELECT, mas ele puxa muitos registros do banco de dados que são consolidados pelo GROUP BYe é bastante lento. Estou pensando que deve haver algo mais rápido, sem o select aninhado.
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é o ID da loja neste caso, só preciso executar essa consulta para uma loja por vez para que isso possa ser considerado uma constante.
Observe que, se eu fizer a consulta acima com o GROUP BY, estou recebendo cerca de 14 registros, o que está correto (quando eu mesmo faço a contagem). O sistema tem alguns milhares de produtos e algumas centenas de lojas, de modo que a tabela de junção product_store é bastante grande. Se eu fizer a consulta acima sem o GROUP BY, estou recebendo ~ 1500 registros que parecem ser a lista de todas as combinações de produtos/lojas de produtos que se encaixam na LEFT JOINcláusula.
Apenas para reiterar, a consulta acima funciona bem (embora nas versões do MySQL > 5.7.5 haverá problemas com o GROUP BY), mas parece ser ineficiente e estou tentando descobrir algo melhor e mais rápido.
ALGUNS DETALHES ADICIONAIS SOBRE O CONTEÚDO DA TABELA
Aqui estão algumas consultas rápidas sobre os produtos e lojas relevantes, etc. Primeiro para mostrar TODOS os produtos correspondentes relevantes -- há 15, todos com 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 |
+-----+----------------------------------+--------+
Agora, observe a junção com a tabela product_store. Há apenas uma entrada para store_id 226 e tem 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 |
+-----+----------------------------------+--------+------+
... Portanto, preciso ser capaz de construir uma consulta que selecione todos os 15 produtos onde global=1 EXCETO o produto onde product_store.show=0, portanto, preciso do conjunto de 14 produtos restantes.
Uma seleção UNION funcionaria de alguma forma? Ou alguma forma de interseção?
Exemplo: http://sqlfiddle.com/#!9/1342cf/5
