[Vtigercrm-developers] vt7.1 Slow?

[Alan Lord]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screenshot from 2017-06-07 15-26-29.png
Type: image/png
Size: 440278 bytes
Desc: not available
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20170607/d28b9803/attachment-0001.png>