[Vtigercrm-developers] vtiger slowing down (a lot)

[Alan Lord]

On 30/09/2022 21:05, Angelo Paglialonga wrote:
> Hi Ruben I still suggest you to apply indexes to the all  fields in 
> select query and check the results if you are curious

I don't think indexing every field is a good idea. There is no point if 
those fields are not being "processed" in some way:

> 
> * When an index is created on the column(s), MySQL also creates a separate file that is sorted, and contains only the field(s) you're interested in sorting on.
> 
> Firstly, the indexes take up disk space. Usually the space usage isn’t significant, but because of creating index on every column in every possible combination, the index file would grow much more quickly than the data file. In the case when a table is of large table size, the index file could reach the operating system’s maximum file size.
> 
> Secondly, the indexes slow down the speed of writing queries, such as INSERT, UPDATE and DELETE. Because MySQL has to internally maintain the “pointers” to the inserted rows in the actual data file, so there is a performance price to pay in case of above said writing queries because every time a record is changed, the indexes must be updated. However, you may be able to write your queries in such a way that do not cause the very noticeable performance degradation.


@Rubén

When I run this query with an explain (I have removed the columns in 
vtiger_invoicecf which do not exists in my database) on a database that 
has 20,000,000 rows in vtiger_crmentity I have no rows where there 
*isn't* an index to use (See screenshot):

On your Explain I am concerned by row 1. It says there are 675575 rows 
and no index to use:

> Extra: Using where; Using temporary; Using filesort

I would have expected this to read:

> Extra: Using where; Using index; Using temporary; Using filesort

(Note the "using index;").

are you missing an index/Primary Key on either vtiger_invoice or 
vtiger_invoicecf?

Also, I do agree about the ORDER BY clause. This is going to kill a 
query where there are a lot of rows order. I would have thought adding 
an index to the modifiedtime column would have improved things 
considerably - I am not sure why you didn't see any improvement.

Finally, I recall many years go thinking that the line

>   AND vtiger_invoice.invoiceid > 0 

was completely useless and tried removing it. I have a suspicion it did 
improve things but I haven't really proved it so I'm not going to say it 
works definitely, but it might be worth trying. IMHO it's completely 
useless because invoiceid is INNER JOINED to crmentity.crmid and as both 
are primary keys of their respective tables, EVEN if there was a row 
with an id of 0, there can only ever be one - so why bother testing 
every row???

HTH

Al
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screenshot from 2022-10-02 11-25-35.png
Type: image/png
Size: 146097 bytes
Desc: not available
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20221002/9386d53b/attachment-0001.png>