[Vtigercrm-developers] Calendar ListView Queries

[Alan Lord]

On 18/05/15 10:35, Vikas Jain wrote:
> Alan,
>
> It was added to force the mysql use the table index, can you try the
> same query by removing the indexes added on activitytype and
> modifiedtime and comment the code and see if the indexes are having any
> influence on the query.

Hi Vikas,

This was quite interesting...

The activitytype index seems to make negligible difference to the query 
time.

I am a bit confused by the modifiedtime index however...

When I ran the query this morning (with the "AND 
vtiger_activity.activityid > 0" line included) the query ran reasonably 
fast with an average of around 0.4 seconds.

I then dropped the modifiedtime index and tried running the same query 
again. The query timed-out and never finished.

I then re-created the modifiedtime index, which took about 12 seconds to 
build, and tried the query again. It still times out.

I did notice that *after* mysql had said the index was created the 
Cardinality figure was very low (like a few hundred) and after several 
minutes it appeared to have increased (it's now 2673279). But the query 
was still not completing.

If I remove the "AND vtiger_activity.activityid > 0" clause from the 
query it returns nice and fast. If I include it again, the query times out.

So I am a bit confused by this index on the modifiedtime column. It 
almost seems like MySQL might have spent the weekend building some kind 
of index in the background which would then explain why it was fine 
before I deleted the index, and why it isn't fine after re-building it 
this again morning. But this would not seem to be the way MySQL works 
from what I have read so I am confused.

Nevertheless, I still do not see why this line is needed at all:

"AND vtiger_activity.activityid > 0"

because this join makes it totally redundant IMHO

> INNER JOIN vtiger_crmentity
>   ON vtiger_activity.activityid = vtiger_crmentity.crmid

And, more to the point, if I remove it from the query it runs fast. With 
it, it is too slow.

Cheers

Al