DisGrouMem index

RT Users
1

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 ?

Ist required , can I drop it??

Regards;
Roy

2

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.

3

If you do not have an index, won’t you need to do a
sequential scan of the full table each time. With 6m
entries, it could take a while.

Cheers,
KenOn 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 ?

Ist required , can I drop it??

Regards;
Roy

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

4

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:

5

Roy,

The schema that came with RT 3.8.5 has the DisGrouMem
index on CachedGroupMembers and not the GrouMem index.
I would drop that latter index.

Regards,
KenOn Wed, Apr 14, 2010 at 02:54:01PM +0100, Raed El-Hames 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

6

From my experience with compound indexes (Informix), the optimiser will use the leading component of the compound, thereafter, it would force a scan of the table.
The column ‘disabled’ is in the compound, and very likely not be the first component and hence, a forced a scan.
Drop the index and you would deprive queries of the first component as an index.
This would be quite painfull if the first was used as a “join” component, and probably is.

Is the index DisGrouMem (GroupId,MemberId,Disabled) required for 3.8

I wouldn’t recommend it.

.

7

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@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.

8

Thanks to everyone reply …
I should have done a bit more research first , I had another index
combining (GroupId,MemberId) that must have been added by me/some one in
my organisation,
and I wrongly assumed that RT created both indexes making the DisGrouMem
(GroupId,MemberId,Disabled) useless.
Been using RT for many years , lost track of what was done when and by
whom …

Once again thanks for all the replies

Regards;
Roy

Ian Pellew wrote: