[rt-users] DisGrouMem index

[Ruslan Zakirov]

Hello Raed,

In theory mysql can use the 3 element index and avoid looking at the
table. If you have query that looks into X columns and all those X
columns are part of an index then mysql doesn't touch table at all and
completes query right from the index.

For this particular tamble more than 50% (may be even 90%) of queries
use MemberId, GroupId and Disabled columns. However, last time I
checked mysql has a bug in optimizer and doesn't use this feature. I
still think RT is better with the three columns variant as it will
bring more speed as soon as bug fixed in mysql (may be it even fixed
in mysql 5.1.some or repo).

Sure, you don't have to have both indexes. It's better to drop one.
Which one to drop is up to you.

On Wed, Apr 14, 2010 at 5:54 PM, Raed El-Hames <rfh at vialtus.com> wrote:
> Jesse;
>
> Ah - In my database I do also have index GrouMem (GroupId,MemberId) on
> CachedGroupMembers
> Your reply prompted me to have a look at the source code and it seems for
> one reason or another I may have created this index myself based on the
> Oracle schema some time in the past few years.
>
> Considering there is (GroupId,MemberId) index, do you still recommend not to
> drop the DisGrouMem index (what will be the reason)?
> I will need to drop one of them and I inclined to drop the 3 element index
> -- unless you tell me not to.
>
> Regards;
>
> Roy
>
> Jesse Vincent wrote:
>>
>> On Wed, Apr 14, 2010 at 02:17:10PM +0100, Raed El-Hames wrote:
>>
>>>
>>> Hi;
>>>
>>> rt-3.8.7
>>> mysql 5.1
>>>
>>> Is the index  DisGrouMem (GroupId,MemberId,Disabled) required for 3.8
>>>
>>> When I did:
>>> select * from CachedGroupMembers where Disabled = 1;
>>> I got back 232 rows out of 5594612
>>>
>>> Which in my opinion makes that index useless ?
>>>
>>
>> My recollection is that MySQL can do just fine using the first component
>> of a compound index.
>>
>>
>>>
>>> Ist required , can I drop it??
>>>
>>
>> I wouldn't recommend it.
>>
>>
>>>
>>> Regards;
>>> Roy
>>>
>>>
>>>
>>>
>>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>>> Buy a copy at http://rtbook.bestpractical.com
>>>
>>
>>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>



-- 
Best regards, Ruslan.