<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Ruslan,<br>
<br>
I have changed those indexes this morning and will monitor the results
today. I created the second new index as CGM2 to prevent the system
complaining about duplicate keys.<br>
<br>
Thanks<br>
<br>
Richard<br>
<br>
Ruslan Zakirov wrote:
<blockquote
cite="mid:589c94400803200940x5401e4a5y190a0fec8af20f0b@mail.gmail.com"
type="cite">
<pre wrap="">4 seconds is still slow, but better than 100-400.
About your indexes. You can and I really suggest to delete the
following indexes on CGM table:
* DROP INDEX GrouMem ON CachedGroupMembers;
* DROP INDEX group1 ON CachedGroupMembers;
* DROP INDEX member1 ON CachedGroupMembers;
And instead create indexes:
CREATE INDEX CGM1 ON CachedGroupMembers(MemberId, GroupId, Disabled);
CREATE INDEX CGM1 ON CachedGroupMembers(MemberId, ImmediateParentId);
Both will be part of 3.8's schema update.
On Wed, Mar 19, 2008 at 8:35 PM, Richard Ellis <a class="moz-txt-link-rfc2396E" href="mailto:Richard.Ellis@sun.com"><Richard.Ellis@sun.com></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap=""> Hi Ruslan,
You are a genius. Response time for the Query Builder is now back to 4
seconds which is good enough for me :0.
Thanks to all your team for all the efforts to work out what was wrong.
Thanks
Richard
Ruslan Zakirov wrote:
Hey, Rechard, the latest results suggest me that we've saddled this
beast :) at least that what explain says and I hope it's correct.
You can check that query again and it should be fast. Wanna try?
You can use SELECT SQL_NO_CACHE ... to make sure it's reproducible and
is not cache hit.
On Wed, Mar 19, 2008 at 7:22 PM, Richard Ellis <a class="moz-txt-link-rfc2396E" href="mailto:Richard.Ellis@sun.com"><Richard.Ellis@sun.com></a>
wrote:
Hi Ruslan,
here's the two sets of results.
Thanks
Richard
</pre>
</blockquote>
<pre wrap=""><!---->
</pre>
</blockquote>
<br>
<div class="moz-signature">-- <br>
<table border="0" cellpadding="0" cellspacing="0" width="519">
<tbody>
<tr valign="top">
<td height="121" width="98"><a href="http://www.sun.com"><img
moz-do-not-send="true" src="file://///home/re100941/6h_top.gif"
alt="Sun.com" border="0" height="92" width="98"></a></td>
<td style="font-family: Helvetica; font-size: 12px;" height="121"
width="249"><b> Richard Ellis </b><br>
Technical Developer, .Sun eBusiness <br>
<br>
<b>Sun Microsystems, Inc.</b><br>
Phone x(70) 24727/+44-1252-424 727<br>
Fax +44 1252 420410<br>
Email <a class="moz-txt-link-abbreviated"
href="mailto:richard.ellis@Sun.COM">richard.ellis@Sun.COM</a><br>
</td>
<td style="font-family: Helvetica; font-size: 12px;" width="172"><img
moz-do-not-send="true" src="file://///home/re100941/q01.gif"
alt="sun.com" height="118" width="172"></td>
</tr>
</tbody>
</table>
</div>
</body>
</html>