<div dir="ltr">There is a strapon query for non admin. Maybe that is hogging time?</div><div class="gmail_extra"><br clear="all"><div><div class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div>With best regards,<br><br>S.T.Prasad (Skype: <a href="mailto:stprasad@stprasad.com" target="_blank">stprasad@stprasad.com</a>)</div><div>Founder and Chief Shikari<br><a href="http://www.vtigress.com" target="_blank">http://www.vtigress.com</a><br>The Purr-fect mate for vTiger<br></div><div>Certified Solution Partner for Asia and Africa</div></div></div></div></div></div></div></div>
<br><div class="gmail_quote">On Wed, Jun 7, 2017 at 8:24 PM, Alan Lord <span dir="ltr"><<a href="mailto:alanslists@gmail.com" target="_blank">alanslists@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">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).<br>
<br>
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?<br>
<br>
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.<br>
<br>
<br>
Al<div><div class="h5"><br>
<br>
<br>
On 07/06/17 15:31, Alan Lord wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div class="h5">
On 07/06/17 14:56, S T Prasad wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hi Alan,<br>
Thats serious horsepower you have.<br>
There probably is some bad query at play. Any clues from your logs<br>
</blockquote>
<br>
There are two queries which seem to be root cause of this:<br>
<br>
The first creates a temporary table which in itself doesn't take any time:<br>
<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
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 <a href="http://vtiger_users.id" rel="noreferrer" target="_blank">vtiger_users.id</a>=vtiger_user2ro<wbr>le.userid INNER JOIN vtiger_role ON vtiger_role.roleid=vtiger_user<wbr>2role.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_p<wbr>er WHERE userid=271 AND tabid=6) UNION (SELECT vtiger_tmp_read_group_sharing_<wbr>per.sharedgroupid FROM vtiger_tmp_read_group_sharing_<wbr>per WHERE userid=271 AND tabid=6);<br>
<br>
Query OK, 188 rows affected (0.00 sec)<br>
Records: 188 Duplicates: 0 Warnings: 0<br>
</blockquote>
<br>
But the second, takes over 35 seconds:<br>
<br>
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 ;-)<br>
<br>
<br>
Al<br>
<br>
<br></div></div>
______________________________<wbr>_________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a><br>
<br>
</blockquote>
<br>
<br>
______________________________<wbr>_________________<br>
<a href="http://www.vtiger.com/" rel="noreferrer" target="_blank">http://www.vtiger.com/</a><br>
</blockquote></div><br></div>