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

[Prasad]

It's a slip - thanks for catching it. Please file an issue on the tracker
<https://code.vtiger.com/vtiger/vtigercrm>.

On Tue, Nov 15, 2022 at 8:12 AM Rubén A. Estrada Orozco <rulotec1 at gmail.com>
wrote:

> 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
> _______________________________________________
> http://www.vtiger.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20221116/976dc859/attachment.html>