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
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
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,
Ken
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)
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.
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,
Ken
On Wed, Apr 14, 2010 at 02:54:01PM +0100, Raed El-Hames wrote:
Jesse;
Ah - In my database I do also have index GrouMem
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
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
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.