Re: partitioning of a table

[Rick James]

KEY `INdexartikelpl` (`A_PL`,`A_nr`),
KEY `Indexpl` (`A_PL`),
Redundant; DROP the second.

KEY `key_groothandel_idx` (`A_GH`),
KEY `Groothandel_idx` (`A_GH`),
Redundant; DROP one of them.

More indexes slows down INSERTs.
FOREIGN KEYs slow down INSERTs significantly.

> query_cache_size 74752
That is so small as to be virtually useless. Suggest 0 and OFF.
To see whether the QC is being efficient, let's see
SHOW GLOBAL STATUS LIKE 'Qc%';

> Using index
Good.

> Key_len = 5
This probably refers to a key that is NULLable. Explicitly declare fields NOT NULL except when you need to use NULL.

> varchar(255)
Use realistic limits.

> int(3)
The "3" means nothing; an INT always takes 4 bytes. Perhaps you meant SMALLINT UNSIGNED?

Use UNSIGNED where appropriate.

> Insert are stand alone actions each at a time
SHOW VARIABLES LIKE 'autocommit';

> so to speed up te table
Is the goal to speed up INSERTs?
Or to speed up SELECTs?

CONSTRAINT `Groothandel` FOREIGN KEY (`A_GH`) REFERENCES `tblgroothandel` (`ghid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `key_groothandel` FOREIGN KEY (`A_GH`) REFERENCES `tblgroothandel` (`ghid`) ON DELETE NO ACTION ON UPDATE NO ACTION
Redundant??

Can we see the SELECT that goes with the EXPLAIN?