Advise on MYSQL Index Creation [closed]
up vote
0
down vote
favorite
I am trying to optimize the MYSQL query below creating indexes, but it seems impossible.
The query:
SELECT `CatalogoCombinacaoAtributo`.`id`,
`CatalogoCombinacaoAtributo`.`nome`,
`CatalogoCombinacaoAtributo`.`name`,
`CatalogoCombinacaoAtributo`.`ordem`,
`CatalogoCombinacaoAtributo`.`cor_primaria`,
`CatalogoCombinacaoAtributo`.`cor_secundaria`,
`CatalogoCombinacaoAtributo`.`tipo_capa`,
`CatalogoCombinacaoAtributo`.`combinacao_classes_adicionais`,
( "" ) AS `CatalogoCombinacaoAtributo__sidebar_classes_adicionais`
FROM `c2419_loja`.`catalogo_combinacao_atributos` AS
`CatalogoCombinacaoAtributo`
INNER JOIN `c2419_loja`.`catalogo_produto_estoques` AS
`CatalogoProdutoEstoque`
ON ( ( ( `CatalogoCombinacaoAtributo`.`id` =
`CatalogoProdutoEstoque`.`combinacao_atributo1_id` )
OR ( `CatalogoCombinacaoAtributo`.`id` =
`CatalogoProdutoEstoque`.`combinacao_atributo2_id` )
OR ( `CatalogoCombinacaoAtributo`.`id` =
`CatalogoProdutoEstoque`.`combinacao_atributo3_id` ) )
AND `CatalogoProdutoEstoque`.`produto_id` IN (
'2895', '2896', '2897', '2898', '2899' ) )
WHERE `CatalogoCombinacaoAtributo`.`combinacao_id` = 31
GROUP BY `CatalogoCombinacaoAtributo`.`id`;
The EXPLAIN:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CatalogoCombinacaoAtributo
type: ref
possible_keys: PRIMARY,id,combinacao_id
key: combinacao_id
key_len: 4
ref: const
rows: 5
filtered: 100.00
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: CatalogoProdutoEstoque
type: ALL
possible_keys: produto_id,combinacao_atributo1_id,combinacao_atributo2_id,combinacao_atributo3_id,combinacao,promocao
key: NULL
key_len: NULL
ref: NULL
rows: 17823
filtered: 95.89
Extra: Using where; Using join buffer (flat, BNL join)
The SHOW PROFILE:
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| Starting | 0.000080 |
| Waiting for query cache lock | 0.000024 |
| Init | 0.000022 |
| Checking query cache for query | 0.001088 |
| Checking permissions | 0.000021 |
| Opening tables | 0.000032 |
| After opening tables | 0.000018 |
| System lock | 0.000018 |
| Table lock | 0.000020 |
| Waiting for query cache lock | 0.005737 |
| Init | 0.000483 |
| Optimizing | 0.000225 |
| Statistics | 0.112147 |
| Preparing | 0.393165 |
| Creating tmp table | 0.000107 |
| Sorting result | 0.000041 |
| Executing | 0.000019 |
| Sending data | 0.029402 |
| Creating sort index | 0.000096 |
| Removing tmp table | 0.000025 |
| Creating sort index | 0.000021 |
| End of update loop | 0.000026 |
| Query end | 0.000030 |
| Commit | 0.000020 |
| Closing tables | 0.000031 |
| Unlocking tables | 0.000018 |
| Closing tables | 0.000025 |
| Starting cleanup | 0.000028 |
| Freeing items | 0.000109 |
| Updating status | 0.000020 |
| Waiting for query cache lock | 0.000217 |
| Updating status | 0.000033 |
| Waiting for query cache lock | 0.000461 |
| Updating status | 0.000024 |
| Storing result in query cache | 0.000392 |
| Logging slow query | 0.000074 |
| Reset for next command | 0.000027 |
+--------------------------------+----------+
I am using MariaDB version 10.3.9.
Please, could someone help me optimize this query and guide me on how to create the right indexes?
mysql indexing mariadb
edited Nov 9 at 16:23
Madhur Bhaiya
15.3k52136
asked Nov 9 at 16:19
Andre Fagundes
62
closed as too broad by Jim Garrison, E_net4, Pearly Spencer, AdrianHHH, Paul Roub Nov 9 at 17:20
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
|
show 1 more comment
up vote
0
down vote
favorite
I am trying to optimize the MYSQL query below creating indexes, but it seems impossible.
The query:
SELECT `CatalogoCombinacaoAtributo`.`id`,
`CatalogoCombinacaoAtributo`.`nome`,
`CatalogoCombinacaoAtributo`.`name`,
`CatalogoCombinacaoAtributo`.`ordem`,
`CatalogoCombinacaoAtributo`.`cor_primaria`,
`CatalogoCombinacaoAtributo`.`cor_secundaria`,
`CatalogoCombinacaoAtributo`.`tipo_capa`,
`CatalogoCombinacaoAtributo`.`combinacao_classes_adicionais`,
( "" ) AS `CatalogoCombinacaoAtributo__sidebar_classes_adicionais`
FROM `c2419_loja`.`catalogo_combinacao_atributos` AS
`CatalogoCombinacaoAtributo`
INNER JOIN `c2419_loja`.`catalogo_produto_estoques` AS
`CatalogoProdutoEstoque`
ON ( ( ( `CatalogoCombinacaoAtributo`.`id` =
`CatalogoProdutoEstoque`.`combinacao_atributo1_id` )
OR ( `CatalogoCombinacaoAtributo`.`id` =
`CatalogoProdutoEstoque`.`combinacao_atributo2_id` )
OR ( `CatalogoCombinacaoAtributo`.`id` =
`CatalogoProdutoEstoque`.`combinacao_atributo3_id` ) )
AND `CatalogoProdutoEstoque`.`produto_id` IN (
'2895', '2896', '2897', '2898', '2899' ) )
WHERE `CatalogoCombinacaoAtributo`.`combinacao_id` = 31
GROUP BY `CatalogoCombinacaoAtributo`.`id`;
The EXPLAIN:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CatalogoCombinacaoAtributo
type: ref
possible_keys: PRIMARY,id,combinacao_id
key: combinacao_id
key_len: 4
ref: const
rows: 5
filtered: 100.00
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: CatalogoProdutoEstoque
type: ALL
possible_keys: produto_id,combinacao_atributo1_id,combinacao_atributo2_id,combinacao_atributo3_id,combinacao,promocao
key: NULL
key_len: NULL
ref: NULL
rows: 17823
filtered: 95.89
Extra: Using where; Using join buffer (flat, BNL join)
The SHOW PROFILE:
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| Starting | 0.000080 |
| Waiting for query cache lock | 0.000024 |
| Init | 0.000022 |
| Checking query cache for query | 0.001088 |
| Checking permissions | 0.000021 |
| Opening tables | 0.000032 |
| After opening tables | 0.000018 |
| System lock | 0.000018 |
| Table lock | 0.000020 |
| Waiting for query cache lock | 0.005737 |
| Init | 0.000483 |
| Optimizing | 0.000225 |
| Statistics | 0.112147 |
| Preparing | 0.393165 |
| Creating tmp table | 0.000107 |
| Sorting result | 0.000041 |
| Executing | 0.000019 |
| Sending data | 0.029402 |
| Creating sort index | 0.000096 |
| Removing tmp table | 0.000025 |
| Creating sort index | 0.000021 |
| End of update loop | 0.000026 |
| Query end | 0.000030 |
| Commit | 0.000020 |
| Closing tables | 0.000031 |
| Unlocking tables | 0.000018 |
| Closing tables | 0.000025 |
| Starting cleanup | 0.000028 |
| Freeing items | 0.000109 |
| Updating status | 0.000020 |
| Waiting for query cache lock | 0.000217 |
| Updating status | 0.000033 |
| Waiting for query cache lock | 0.000461 |
| Updating status | 0.000024 |
| Storing result in query cache | 0.000392 |
| Logging slow query | 0.000074 |
| Reset for next command | 0.000027 |
+--------------------------------+----------+
I am using MariaDB version 10.3.9.
Please, could someone help me optimize this query and guide me on how to create the right indexes?
mysql indexing mariadb
edited Nov 9 at 16:23
Madhur Bhaiya
15.3k52136
asked Nov 9 at 16:19
Andre Fagundes
62
closed as too broad by Jim Garrison, E_net4, Pearly Spencer, AdrianHHH, Paul Roub Nov 9 at 17:20
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Welcome to StackOverflow. As explained in the tour, this site is a repository of useful questions and their answers, not a help forum. Your question is MUCH too broad for this site. Please take the tour, visit the help center and especially read How to Ask and Why is “Can someone help me?” not an actual question? to learn how to use this site effectively.
– Jim Garrison
Nov 9 at 16:31
You can't optimize this while you have the repeating group of columns:CatalogoProdutoEstoque.combinacao_atributo1_idand...2_idand...3_id. You have a many-to-many relationship, and you need to create another table to represent it.
– Bill Karwin
Nov 9 at 16:32
Voting to reopen; this seems similar to lots of other Questions that ask about performance. If it gets reopened, please provideSHOW CREATE TABLE.
– Rick James
Nov 9 at 21:28
catalogo_combinacao_atributos:INDEX(combinacao_id, id); catalogo_produto_estoques:INDEX(producto_id). Other optimizations include usingEXISTS ( SELECT 1 FROM catalogo_produto_estoques ... )or turning theORsinto ` UNION`.
– Rick James
Nov 9 at 21:33
Guidance on building indexes: mysql.rjweb.org/doc.php/index_cookbook_mysql However, your query is too complex for it to give you a full answer.
– Rick James
Nov 9 at 21:35
|
show 1 more comment
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I am trying to optimize the MYSQL query below creating indexes, but it seems impossible.
The query:
SELECT `CatalogoCombinacaoAtributo`.`id`,
`CatalogoCombinacaoAtributo`.`nome`,
`CatalogoCombinacaoAtributo`.`name`,
`CatalogoCombinacaoAtributo`.`ordem`,
`CatalogoCombinacaoAtributo`.`cor_primaria`,
`CatalogoCombinacaoAtributo`.`cor_secundaria`,
`CatalogoCombinacaoAtributo`.`tipo_capa`,
`CatalogoCombinacaoAtributo`.`combinacao_classes_adicionais`,
( "" ) AS `CatalogoCombinacaoAtributo__sidebar_classes_adicionais`
FROM `c2419_loja`.`catalogo_combinacao_atributos` AS
`CatalogoCombinacaoAtributo`
INNER JOIN `c2419_loja`.`catalogo_produto_estoques` AS
`CatalogoProdutoEstoque`
ON ( ( ( `CatalogoCombinacaoAtributo`.`id` =
`CatalogoProdutoEstoque`.`combinacao_atributo1_id` )
OR ( `CatalogoCombinacaoAtributo`.`id` =
`CatalogoProdutoEstoque`.`combinacao_atributo2_id` )
OR ( `CatalogoCombinacaoAtributo`.`id` =
`CatalogoProdutoEstoque`.`combinacao_atributo3_id` ) )
AND `CatalogoProdutoEstoque`.`produto_id` IN (
'2895', '2896', '2897', '2898', '2899' ) )
WHERE `CatalogoCombinacaoAtributo`.`combinacao_id` = 31
GROUP BY `CatalogoCombinacaoAtributo`.`id`;
The EXPLAIN:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CatalogoCombinacaoAtributo
type: ref
possible_keys: PRIMARY,id,combinacao_id
key: combinacao_id
key_len: 4
ref: const
rows: 5
filtered: 100.00
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: CatalogoProdutoEstoque
type: ALL
possible_keys: produto_id,combinacao_atributo1_id,combinacao_atributo2_id,combinacao_atributo3_id,combinacao,promocao
key: NULL
key_len: NULL
ref: NULL
rows: 17823
filtered: 95.89
Extra: Using where; Using join buffer (flat, BNL join)
The SHOW PROFILE:
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| Starting | 0.000080 |
| Waiting for query cache lock | 0.000024 |
| Init | 0.000022 |
| Checking query cache for query | 0.001088 |
| Checking permissions | 0.000021 |
| Opening tables | 0.000032 |
| After opening tables | 0.000018 |
| System lock | 0.000018 |
| Table lock | 0.000020 |
| Waiting for query cache lock | 0.005737 |
| Init | 0.000483 |
| Optimizing | 0.000225 |
| Statistics | 0.112147 |
| Preparing | 0.393165 |
| Creating tmp table | 0.000107 |
| Sorting result | 0.000041 |
| Executing | 0.000019 |
| Sending data | 0.029402 |
| Creating sort index | 0.000096 |
| Removing tmp table | 0.000025 |
| Creating sort index | 0.000021 |
| End of update loop | 0.000026 |
| Query end | 0.000030 |
| Commit | 0.000020 |
| Closing tables | 0.000031 |
| Unlocking tables | 0.000018 |
| Closing tables | 0.000025 |
| Starting cleanup | 0.000028 |
| Freeing items | 0.000109 |
| Updating status | 0.000020 |
| Waiting for query cache lock | 0.000217 |
| Updating status | 0.000033 |
| Waiting for query cache lock | 0.000461 |
| Updating status | 0.000024 |
| Storing result in query cache | 0.000392 |
| Logging slow query | 0.000074 |
| Reset for next command | 0.000027 |
+--------------------------------+----------+
I am using MariaDB version 10.3.9.
Please, could someone help me optimize this query and guide me on how to create the right indexes?
mysql indexing mariadb
edited Nov 9 at 16:23
Madhur Bhaiya
15.3k52136
asked Nov 9 at 16:19
Andre Fagundes
62
I am trying to optimize the MYSQL query below creating indexes, but it seems impossible.
The query:
SELECT `CatalogoCombinacaoAtributo`.`id`,
`CatalogoCombinacaoAtributo`.`nome`,
`CatalogoCombinacaoAtributo`.`name`,
`CatalogoCombinacaoAtributo`.`ordem`,
`CatalogoCombinacaoAtributo`.`cor_primaria`,
`CatalogoCombinacaoAtributo`.`cor_secundaria`,
`CatalogoCombinacaoAtributo`.`tipo_capa`,
`CatalogoCombinacaoAtributo`.`combinacao_classes_adicionais`,
( "" ) AS `CatalogoCombinacaoAtributo__sidebar_classes_adicionais`
FROM `c2419_loja`.`catalogo_combinacao_atributos` AS
`CatalogoCombinacaoAtributo`
INNER JOIN `c2419_loja`.`catalogo_produto_estoques` AS
`CatalogoProdutoEstoque`
ON ( ( ( `CatalogoCombinacaoAtributo`.`id` =
`CatalogoProdutoEstoque`.`combinacao_atributo1_id` )
OR ( `CatalogoCombinacaoAtributo`.`id` =
`CatalogoProdutoEstoque`.`combinacao_atributo2_id` )
OR ( `CatalogoCombinacaoAtributo`.`id` =
`CatalogoProdutoEstoque`.`combinacao_atributo3_id` ) )
AND `CatalogoProdutoEstoque`.`produto_id` IN (
'2895', '2896', '2897', '2898', '2899' ) )
WHERE `CatalogoCombinacaoAtributo`.`combinacao_id` = 31
GROUP BY `CatalogoCombinacaoAtributo`.`id`;
The EXPLAIN:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CatalogoCombinacaoAtributo
type: ref
possible_keys: PRIMARY,id,combinacao_id
key: combinacao_id
key_len: 4
ref: const
rows: 5
filtered: 100.00
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: CatalogoProdutoEstoque
type: ALL
possible_keys: produto_id,combinacao_atributo1_id,combinacao_atributo2_id,combinacao_atributo3_id,combinacao,promocao
key: NULL
key_len: NULL
ref: NULL
rows: 17823
filtered: 95.89
Extra: Using where; Using join buffer (flat, BNL join)
The SHOW PROFILE:
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| Starting | 0.000080 |
| Waiting for query cache lock | 0.000024 |
| Init | 0.000022 |
| Checking query cache for query | 0.001088 |
| Checking permissions | 0.000021 |
| Opening tables | 0.000032 |
| After opening tables | 0.000018 |
| System lock | 0.000018 |
| Table lock | 0.000020 |
| Waiting for query cache lock | 0.005737 |
| Init | 0.000483 |
| Optimizing | 0.000225 |
| Statistics | 0.112147 |
| Preparing | 0.393165 |
| Creating tmp table | 0.000107 |
| Sorting result | 0.000041 |
| Executing | 0.000019 |
| Sending data | 0.029402 |
| Creating sort index | 0.000096 |
| Removing tmp table | 0.000025 |
| Creating sort index | 0.000021 |
| End of update loop | 0.000026 |
| Query end | 0.000030 |
| Commit | 0.000020 |
| Closing tables | 0.000031 |
| Unlocking tables | 0.000018 |
| Closing tables | 0.000025 |
| Starting cleanup | 0.000028 |
| Freeing items | 0.000109 |
| Updating status | 0.000020 |
| Waiting for query cache lock | 0.000217 |
| Updating status | 0.000033 |
| Waiting for query cache lock | 0.000461 |
| Updating status | 0.000024 |
| Storing result in query cache | 0.000392 |
| Logging slow query | 0.000074 |
| Reset for next command | 0.000027 |
+--------------------------------+----------+
I am using MariaDB version 10.3.9.
Please, could someone help me optimize this query and guide me on how to create the right indexes?
mysql indexing mariadb
mysql indexing mariadb
edited Nov 9 at 16:23
Madhur Bhaiya
15.3k52136
asked Nov 9 at 16:19
Andre Fagundes
62
edited Nov 9 at 16:23
Madhur Bhaiya
15.3k52136
asked Nov 9 at 16:19
Andre Fagundes
62
edited Nov 9 at 16:23
Madhur Bhaiya
15.3k52136
edited Nov 9 at 16:23
Madhur Bhaiya
15.3k52136
edited Nov 9 at 16:23
Madhur Bhaiya
15.3k52136
15.3k52136
asked Nov 9 at 16:19
Andre Fagundes
62
asked Nov 9 at 16:19
Andre Fagundes
62
asked Nov 9 at 16:19
Andre Fagundes
62
62
closed as too broad by Jim Garrison, E_net4, Pearly Spencer, AdrianHHH, Paul Roub Nov 9 at 17:20
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as too broad by Jim Garrison, E_net4, Pearly Spencer, AdrianHHH, Paul Roub Nov 9 at 17:20
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Welcome to StackOverflow. As explained in the tour, this site is a repository of useful questions and their answers, not a help forum. Your question is MUCH too broad for this site. Please take the tour, visit the help center and especially read How to Ask and Why is “Can someone help me?” not an actual question? to learn how to use this site effectively.
– Jim Garrison
Nov 9 at 16:31
You can't optimize this while you have the repeating group of columns:CatalogoProdutoEstoque.combinacao_atributo1_idand...2_idand...3_id. You have a many-to-many relationship, and you need to create another table to represent it.
– Bill Karwin
Nov 9 at 16:32
Voting to reopen; this seems similar to lots of other Questions that ask about performance. If it gets reopened, please provideSHOW CREATE TABLE.
– Rick James
Nov 9 at 21:28
catalogo_combinacao_atributos:INDEX(combinacao_id, id); catalogo_produto_estoques:INDEX(producto_id). Other optimizations include usingEXISTS ( SELECT 1 FROM catalogo_produto_estoques ... )or turning theORsinto ` UNION`.
– Rick James
Nov 9 at 21:33
Guidance on building indexes: mysql.rjweb.org/doc.php/index_cookbook_mysql However, your query is too complex for it to give you a full answer.
– Rick James
Nov 9 at 21:35
|
show 1 more comment
Welcome to StackOverflow. As explained in the tour, this site is a repository of useful questions and their answers, not a help forum. Your question is MUCH too broad for this site. Please take the tour, visit the help center and especially read How to Ask and Why is “Can someone help me?” not an actual question? to learn how to use this site effectively.
– Jim Garrison
Nov 9 at 16:31
You can't optimize this while you have the repeating group of columns:CatalogoProdutoEstoque.combinacao_atributo1_idand...2_idand...3_id. You have a many-to-many relationship, and you need to create another table to represent it.
– Bill Karwin
Nov 9 at 16:32
Voting to reopen; this seems similar to lots of other Questions that ask about performance. If it gets reopened, please provideSHOW CREATE TABLE.
– Rick James
Nov 9 at 21:28
catalogo_combinacao_atributos:INDEX(combinacao_id, id); catalogo_produto_estoques:INDEX(producto_id). Other optimizations include usingEXISTS ( SELECT 1 FROM catalogo_produto_estoques ... )or turning theORsinto ` UNION`.
– Rick James
Nov 9 at 21:33
Guidance on building indexes: mysql.rjweb.org/doc.php/index_cookbook_mysql However, your query is too complex for it to give you a full answer.
– Rick James
Nov 9 at 21:35
Welcome to StackOverflow. As explained in the tour, this site is a repository of useful questions and their answers, not a help forum. Your question is MUCH too broad for this site. Please take the tour, visit the help center and especially read How to Ask and Why is “Can someone help me?” not an actual question? to learn how to use this site effectively.
– Jim Garrison
Nov 9 at 16:31
Welcome to StackOverflow. As explained in the tour, this site is a repository of useful questions and their answers, not a help forum. Your question is MUCH too broad for this site. Please take the tour, visit the help center and especially read How to Ask and Why is “Can someone help me?” not an actual question? to learn how to use this site effectively.
– Jim Garrison
Nov 9 at 16:31
You can't optimize this while you have the repeating group of columns:
CatalogoProdutoEstoque.combinacao_atributo1_id and ...2_id and ...3_id. You have a many-to-many relationship, and you need to create another table to represent it.– Bill Karwin
Nov 9 at 16:32
You can't optimize this while you have the repeating group of columns:
CatalogoProdutoEstoque.combinacao_atributo1_id and ...2_id and ...3_id. You have a many-to-many relationship, and you need to create another table to represent it.– Bill Karwin
Nov 9 at 16:32
Voting to reopen; this seems similar to lots of other Questions that ask about performance. If it gets reopened, please provide
SHOW CREATE TABLE.– Rick James
Nov 9 at 21:28
Voting to reopen; this seems similar to lots of other Questions that ask about performance. If it gets reopened, please provide
SHOW CREATE TABLE.– Rick James
Nov 9 at 21:28
catalogo_combinacao_atributos:
INDEX(combinacao_id, id); catalogo_produto_estoques: INDEX(producto_id). Other optimizations include using EXISTS ( SELECT 1 FROM catalogo_produto_estoques ... ) or turning the ORs into ` UNION`.– Rick James
Nov 9 at 21:33
catalogo_combinacao_atributos:
INDEX(combinacao_id, id); catalogo_produto_estoques: INDEX(producto_id). Other optimizations include using EXISTS ( SELECT 1 FROM catalogo_produto_estoques ... ) or turning the ORs into ` UNION`.– Rick James
Nov 9 at 21:33
Guidance on building indexes: mysql.rjweb.org/doc.php/index_cookbook_mysql However, your query is too complex for it to give you a full answer.
– Rick James
Nov 9 at 21:35
Guidance on building indexes: mysql.rjweb.org/doc.php/index_cookbook_mysql However, your query is too complex for it to give you a full answer.
– Rick James
Nov 9 at 21:35
|
show 1 more comment
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Welcome to StackOverflow. As explained in the tour, this site is a repository of useful questions and their answers, not a help forum. Your question is MUCH too broad for this site. Please take the tour, visit the help center and especially read How to Ask and Why is “Can someone help me?” not an actual question? to learn how to use this site effectively.
– Jim Garrison
Nov 9 at 16:31
You can't optimize this while you have the repeating group of columns:
CatalogoProdutoEstoque.combinacao_atributo1_idand...2_idand...3_id. You have a many-to-many relationship, and you need to create another table to represent it.– Bill Karwin
Nov 9 at 16:32
Voting to reopen; this seems similar to lots of other Questions that ask about performance. If it gets reopened, please provide
SHOW CREATE TABLE.– Rick James
Nov 9 at 21:28
catalogo_combinacao_atributos:
INDEX(combinacao_id, id); catalogo_produto_estoques:INDEX(producto_id). Other optimizations include usingEXISTS ( SELECT 1 FROM catalogo_produto_estoques ... )or turning theORsinto ` UNION`.– Rick James
Nov 9 at 21:33
Guidance on building indexes: mysql.rjweb.org/doc.php/index_cookbook_mysql However, your query is too complex for it to give you a full answer.
– Rick James
Nov 9 at 21:35