Canias optimizations

Canias is a great ERP product but there is still some potential to improve it. We only focus on MyISAM/InnoDB installations of Canias v6.0.2 in this article. For other DB back-ends those recommendations might be obsolete.

A general problem is, that in InnoDB Primary Keys are crucial in MySQL. Canias often lacks a Primary Key. In this case InnoDB will use the Unique Key for the Primary Key.

In InnoDB data are sorted by the Primary Key so it is a good idea to have the Primary Key on CLIENT and COMPANY as well to get proper sorting of the rows.

This has been implemented very in-consequent in Canias development.

In the following table there is NO Primary Key. Thus MySQL will elect the UNIQUE KEY IASSALITEM as Primary Key and sort the rows according to the DOCNUM instead of (CLIENT, COMPANY, DOCTYPE, DOCNUM, ITEMNUM, SETITEMNUM) what logically would be correct...

Table IASSALITEM

CREATE TABLE `IASSALITEM` (
  ...
  UNIQUE KEY `IASSALITEM_U1` (`DOCNUM`,`ITEMNUM`,`SETITEMNUM`,`DOCTYPE`,`COMPANY`,`CLIENT`),
  KEY `IASSALITEM_1` (`DOCNUM`,`DOCTYPE`,`COMPANY`,`CLIENT`),
  KEY `IASSALITEM_10` (`CLIENT`,`COMPANY`,`DOCTYPE`,`DOCNUM`,`VALIDFROM`,`ISORDCHAR`,`ORDTYPE`,`ORDSTAT`,`ISTEXT`,`ISSTOP`),
  KEY `IASSALITEM_2` (`MTEXT`,`MATERIAL`,`DOCNUM`,`DOCTYPE`,`COMPANY`,`CLIENT`),
  KEY `IASSALITEM_3` (`CLIENT`,`COMPANY`,`DOCTYPE`,`DOCNUM`,`CUSTOMER`),
  KEY `IASSALITEM_4` (`CLIENT`,`VALIDFROM`,`MATERIAL`),
  KEY `IASSALITEM_5` (`CLIENT`,`COMPANY`,`DOCTYPE`,`DOCNUM`,`ISTEXT`,`DELSTAT`,`ISDELCHAR`,`ISORDCHAR`,`ISSTOP`),
  KEY `IASSALITEM_6` (`CLIENT`,`COMPANY`,`DOCTYPE`,`DOCNUM`,`MATERIAL`,`MTEXT`,`PLANT`,`WAREHOUSE`),
  KEY `IASSALITEM_12` (`CLIENT`,`COMPANY`,`MATERIAL`,`WAREHOUSE`,`DOCTYPE`,`DOCNUM`,`ISDELETE`,`ISDELCHAR`,`ISSTOP`,`DELTYPE`,`DELSTAT`,`ISTEXT`),
  KEY `IASSALITEM_11` (`CLIENT`,`COMPANY`,`MATERIAL`,`WAREHOUSE`,`DOCTYPE`,`DOCNUM`,`ISDELETE`,`ISORDCHAR`,`ISSTOP`,`ORDTYPE`,`ORDSTAT`,`ISTEXT`),
  KEY `IASSALITEM_RF` (`CLIENT`,`COMPANY`,`REFDOCTYPE`,`REFDOCNUM`),
  KEY `IASSALITEM_FH` (`CLIENT`,`COMPANY`,`DOCTYPE`,`DOCNUM`,`MATERIAL`,`DOYMASTERITM`),
  KEY `IASSALITEM_13` (`CLIENT`,`COMPANY`,`PLANT`,`MATERIAL`,`VOPTIONS`,`ISORDCHAR`,`ORDSTAT`),
  KEY `IASSALITEM_14` (`CLIENT`,`COMPANY`,`MATERIAL`,`WAREHOUSE`,`ISORDCHAR`,`ORDTYPE`,`ORDSTAT`,`ISDELETE`,`ISDELCHAR`,`ISSTOP`),
  KEY `IASSALITEM_15` (`CLIENT`,`COMPANY`,`MATERIAL`,`WAREHOUSE`,`ISDELETE`,`ISSTOP`,`ISDELCHAR`,`DELTYPE`,`DELSTAT`,`ISTEXT`),
  KEY `IASSALITEM_16` (`DESDLVDATE`,`DOCNUM`,`DOCTYPE`,`COMPANY`,`CLIENT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

More things about Canias clean-up and optimizations will follow...