The compound index will only work on the one situation where you are
processing field1+field2+field3 ("ORDER BY field1+field2+field3") while
using individual indexes lets Rushmore use the for more operations (like
WHERE field1=this and Field2=that). Rushmore is primarily focused on the
WHERE clause, since you lose speed reading records you don't have to.In nearly all cases, individual, atomic indexes, one field each, are better than multiple. However, in optimizing a single statement, the key is to ensure the LEFT SIDE of the WHERE expressions are EXACTLY the same as the expression used to create the index, such as: WHERE STR(iClientID,8) = "12345678" will only work if you have an index created with that exact same expression, INDEX on STR(iClientID,8) TAG AReallyDumbIndex On Wed, Jul 16, 2014 at 6:05 PM, Jeff Johnson <j...@san-dc.com> wrote: > Michael: I was going to say that but I didn't have the guts. ;^) > > > On 7/16/2014 2:41 PM, mbsoftwaresoluti...@mbsoftwaresolutions.com wrote: > >> I'm gonna make a wager and say "NO", and I know that's a large risky >> wager seeing how it's Ted's comment (and we all know he's an ace!). >> >> Why not the compound index as you (Jeff) described? I would have said >> the same thing. >> >> --Mike >> >> >> >> On 2014-07-16 17:23, Jeff Johnson wrote: >> >>> Ted: So these indexes are created so that when you run that select >>> statement it will put them in order? >>> >>> Interesting. >>> >>> On 7/16/2014 1:56 PM, Ted Roche wrote: >>> >>>> Sytze: >>>> >>>> I've been surprised to find everyone works with a "large amount of data" >>>> but for some of us, that's thirty thousand records and for others it is >>>> thirty million. >>>> >>>> >>>> INDEX ON pcode TAG pcode >>>> INDEX ON pcode2 TAG pcode2 >>>> INDEX ON pcode3 TAG pcode3 >>>> INDEX ON DELETED() TAG DELETED >>>> >>>> >>>> >>>> On Wed, Jul 16, 2014 at 4:40 PM, Sytze de Boer <sytze.k...@gmail.com> >>>> wrote: >>>> >>>> After yesterday, with heart in mouth, I ask the following >>>>> >>>>> I have a table with large amount of data >>>>> It contains Master contract, sub contract, sub-sub contract >>>>> pcode N(5) >>>>> pcode2 N(7) >>>>> pcode3 N(9) >>>>> >>>>> Example >>>>> 3770 >>>>> 3770, 377001 >>>>> 3770, 377001, 37700101 >>>>> 3770, 377001, 37700102 >>>>> 3770, 377002 >>>>> 3770, 377002, 37700201 >>>>> 3770, 377002, 37700202 >>>>> >>>>> When I do a select statement, I seem to get them in correct order with >>>>> select * from contract order by pcode,pcode2,pcode3 etc >>>>> >>>>> But if I want to create an index, I lose my head. >>>>> >>>>> Any pointers? >>>>> >>>>> -- >>>>> Kind regards, >>>>> Sytze de Boer >>>>> Kiss Software >>>>> >>>>> >>>>> --- StripMime Report -- processed MIME parts --- >>>>> multipart/alternative >>>>> text/plain (text body -- kept) >>>>> text/html >>>>> --- >>>>> [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CACW6n4tFdkede=k-6ncalzbdmx9fdhpw8ey2dtedllxomr0...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
