[rt-users] DisGrouMem index
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
Re: [rt-users] DisGrouMem index
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
Re: [rt-users] DisGrouMem index
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) 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 Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] DisGrouMem index
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
Re: [rt-users] DisGrouMem index
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 (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 Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] DisGrouMem index
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. . Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] DisGrouMem index
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 r...@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. Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] DisGrouMem index
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: 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. . 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
