2 tmp_table
source | link
  • You do InnoDB a disservice by not providing an explicit PRIMARY KEY. Look at the data to see if an existing column or combination of columns is unique.

  • Perhaps EAN13 could be the PK of product_base?

  • Perhaps (EAN13, attribute_type) could be the PK for attribute?

  • In one table modified is a string type, in another it is DATETIME. ?? Anyway, do you use the column? If not removing it would make the table smaller, thereby possibly leading to a faster query.

  • As already noted, the type inconsistency of EAN13 led to a serious inefficiency. This is because VARCHAR = int-constant does not optimize well. Either change the column to be BIGINT (if appropriate) or add quotes around the constant (to make it a string).

  • These 3 tables smell like an "Entity-Attribute-Value" schema, which can usually be implemented with fewer tables -- hence more efficiently. Is it EAV?

  • You "can't tune your way out of a performance problem". But if you also provided SHOW GLOBAL STATUS; after being up at least a day, I will review the VARIABLES you already provided.

  • These are dangerously high; don't make them more than 1% of RAM: max_heap_table_size = 2G and tmp_table_size = 2G.

  • You do InnoDB a disservice by not providing an explicit PRIMARY KEY. Look at the data to see if an existing column or combination of columns is unique.

  • Perhaps EAN13 could be the PK of product_base?

  • Perhaps (EAN13, attribute_type) could be the PK for attribute?

  • In one table modified is a string type, in another it is DATETIME. ?? Anyway, do you use the column? If not removing it would make the table smaller, thereby possibly leading to a faster query.

  • As already noted, the type inconsistency of EAN13 led to a serious inefficiency. This is because VARCHAR = int-constant does not optimize well. Either change the column to be BIGINT (if appropriate) or add quotes around the constant (to make it a string).

  • These 3 tables smell like an "Entity-Attribute-Value" schema, which can usually be implemented with fewer tables -- hence more efficiently. Is it EAV?

  • You "can't tune your way out of a performance problem". But if you also provided SHOW GLOBAL STATUS; after being up at least a day, I will review the VARIABLES you already provided.

  • You do InnoDB a disservice by not providing an explicit PRIMARY KEY. Look at the data to see if an existing column or combination of columns is unique.

  • Perhaps EAN13 could be the PK of product_base?

  • Perhaps (EAN13, attribute_type) could be the PK for attribute?

  • In one table modified is a string type, in another it is DATETIME. ?? Anyway, do you use the column? If not removing it would make the table smaller, thereby possibly leading to a faster query.

  • As already noted, the type inconsistency of EAN13 led to a serious inefficiency. This is because VARCHAR = int-constant does not optimize well. Either change the column to be BIGINT (if appropriate) or add quotes around the constant (to make it a string).

  • These 3 tables smell like an "Entity-Attribute-Value" schema, which can usually be implemented with fewer tables -- hence more efficiently. Is it EAV?

  • You "can't tune your way out of a performance problem". But if you also provided SHOW GLOBAL STATUS; after being up at least a day, I will review the VARIABLES you already provided.

  • These are dangerously high; don't make them more than 1% of RAM: max_heap_table_size = 2G and tmp_table_size = 2G.

1
source | link

  • You do InnoDB a disservice by not providing an explicit PRIMARY KEY. Look at the data to see if an existing column or combination of columns is unique.

  • Perhaps EAN13 could be the PK of product_base?

  • Perhaps (EAN13, attribute_type) could be the PK for attribute?

  • In one table modified is a string type, in another it is DATETIME. ?? Anyway, do you use the column? If not removing it would make the table smaller, thereby possibly leading to a faster query.

  • As already noted, the type inconsistency of EAN13 led to a serious inefficiency. This is because VARCHAR = int-constant does not optimize well. Either change the column to be BIGINT (if appropriate) or add quotes around the constant (to make it a string).

  • These 3 tables smell like an "Entity-Attribute-Value" schema, which can usually be implemented with fewer tables -- hence more efficiently. Is it EAV?

  • You "can't tune your way out of a performance problem". But if you also provided SHOW GLOBAL STATUS; after being up at least a day, I will review the VARIABLES you already provided.