[Vtigercrm-developers] createing invoices takes too long: vtiger_inventorychargesrel has no primary key

[Rubén A. Estrada Orozco]

There's this table: vtiger_inventorychargesrel
When creating an invoice (I suspect the same applies for other inventory
modules), a query like this gets executed several times:

SELECT * FROM vtiger_inventorychargesrel WHERE recordid = 4535638

On a large DB with 1.5 million records on that table, each such query takes
~0.6 seconds. The creation of a single invoice was taking around 6 seconds.

The table is created here modules/Migration/schema/660_to_700.php with this
query:
CREATE TABLE vtiger_inventorychargesrel(recordid INT(19) NOT NULL, charges
TEXT)

As far as I can tell, there should be a single record  per invoice, so I
added a primary key with:
ALTER TABLE vtiger_inventorychargesrel ADD PRIMARY KEY(recordid)

After that, creation of new invoices was taking 300 milliseconds instead of
6 seconds.

Why was that table created without a primary key or an index? Was it just
overlooked / forgotten or is there a good reason for it?

Saludos

Rubén
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20221114/d251a604/attachment.html>