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.
EAN13could be the PK of
(EAN13, attribute_type)could be the PK for
In one table
modifiedis 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
EAN13led 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
VARIABLESyou already provided.
These are dangerously high; don't make them more than 1% of RAM:
max_heap_table_size = 2Gand
tmp_table_size = 2G.