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

[Rubén A. Estrada Orozco]

Sorry that I didn't mention this earlier but, shouldn't there be a foreign
key constraint too with an on delete cascade? I have noticed previously
that when you delete something from the recycle bin, there are orphan
records in some tables. With the foreign key and the on delete cascade,
those records get deleted automatically.

Saludos

Rubén


On Wed, Nov 16, 2022 at 1:22 PM Prasad <prasad at vtiger.com> wrote:

> Fixed - https://code.vtiger.com/vtiger/vtigercrm/issues/1743
>
> On Wed, Nov 16, 2022 at 6:18 PM Prasad <prasad at vtiger.com> wrote:
>
>> 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/
>>
>> _______________________________________________
> http://www.vtiger.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20221116/d99f1d89/attachment.html>