[Vtigercrm-developers] vt7.1 Slow?

[Alan Lord]

It would appear to be the "ORDER BY" clause which is slowing down the 
query mentioned in the previous post (and there *is* an index on the 
modifiedtime column).

But what I really don't get, is why it takes so long to load a single 
record? Why are these two queries being generated *after* the ListView 
has already been displayed and I click on one record to view it?

Can anyone else migrate a system with a decent amount of data in it and 
try this please? As an admin user it's not too bad, but still pretty 
poor, but as a non-admin this is unusable.


Al


On 07/06/17 15:31, Alan Lord wrote:
> On 07/06/17 14:56, S T Prasad wrote:
>> Hi Alan,
>> Thats serious horsepower you have.
>> There probably is some bad query at play. Any clues from your logs
> 
> There are two queries which seem to be root cause of this:
> 
> The first creates a temporary table which in itself doesn't take any time:
> 
> 
>> mysql> create temporary table IF NOT EXISTS vt_tmp_u271_t6(id int(11) 
>> primary key) ignore (SELECT 271 as id) UNION (SELECT 
>> vtiger_user2role.userid AS userid FROM vtiger_user2role INNER JOIN 
>> vtiger_users ON vtiger_users.id=vtiger_user2role.userid INNER JOIN 
>> vtiger_role ON  vtiger_role.roleid=vtiger_user2role.roleid WHERE 
>> vtiger_role.parentrole like 'H1::H2::H4::%') UNION (SELECT groupid 
>> FROM vtiger_groups WHERE  groupid IN (56)) UNION  (SELECT shareduserid 
>> FROM vtiger_tmp_read_user_sharing_per WHERE userid=271 AND tabid=6) 
>> UNION (SELECT  vtiger_tmp_read_group_sharing_per.sharedgroupid FROM 
>> vtiger_tmp_read_group_sharing_per WHERE userid=271 AND tabid=6);
>>
>> Query OK, 188 rows affected (0.00 sec)
>> Records: 188  Duplicates: 0  Warnings: 0
> 
> But the second, takes over 35 seconds:
> 
> I can't cut and paste it due to customer confidentiality but here is a 
> smudged screenshot of me running the query on the command line of the 
> MySQL server. Note the time at the bottom of the page ;-)
> 
> 
> Al
> 
> 
> _______________________________________________
> http://www.vtiger.com/
>