Compound index usage.

Hi, I have this basic question regarding compound index usage. I read on informix 10 guide on ibm website that for compundindexes to work, the first one has to be equality. But when i do execute explain on this query it does seem to use the index. So does this work? Should i disregard what ive read ststing that first field needs to be equality? I have this index actually. Index defined is (date, string2, string1) Select * from tbl_sample where date >= ? And date <= ? And string1 =? Thanks!

On Thu, Feb 2, 2012 at 08:11, NATYURAL HORACIO <horacio.natyural@gmail.com>wrote: > I have this basic question regarding compound index usage. > I read on informix 10 guide on ibm website that for compundindexes to work, > the first one has to be equality. But when i do execute explain on this > query > it does seem to use the index. So does this work? Should i disregard what > ive > read ststing that first field needs to be equality? > > I have this index actually. > Index defined is > > (date, string2, string1) > > Select * from tbl_sample where date >= ? And date <= ? And string1 =?> To be useful, the leading column of the index (the date column here) has to be 'usable'. In this query, it is; the server can use the index to find the rows between the two DATE parameter value, and then the key value also provides an answer to the second condition; the row only needs to be fetched from disk if both conditions are accurate. Let's consider some other indexes: (string1, date, string2) -- this could be used too (date, string1, string2) -- this could be used too (string1, string2, date) -- this could be used too (string2, date, string1) -- this could not be used; there is no criterion on the leading column. (string2, string1, date) -- this could not be used either, for the same reason (date) -- this could be used, but would be less effective as the rows would have to be fetched to establish whether the other condition is true (string1) -- this could be used, with the same justification. (date, string1) -- this could be used and would be marginally more efficient for this query (than the current index) (string1, date) -- this could be used and would be marginally more efficient for this query (than the current index) (string2) -- this could not be used There are a few permutations not mentioned, but I trust you can see what the answers would be. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --20cf3026697acbc7c904b7fd9b9a

I've found it, and it looks similar in V10 and V11.7. I believe the manual is wrong (on V11.7 it does not even consider INDEX SELF JOIN). It would be interesting to see the query plan for your query. Regards On Thu, Feb 2, 2012 at 4:14 PM, Fernando Nunes <domusonline@gmail.com>wrote: > Can you point the documentation you read? > V10 infocenter start is here: > > http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp > > If you could find the part you read it could be useful to understand the > situation. > Regards. > > > > On Thu, Feb 2, 2012 at 4:11 PM, NATYURAL HORACIO < > horacio.natyural@gmail.com> wrote: > >> Hi, >> >> I have this basic question regarding compound index usage. >> I read on informix 10 guide on ibm website that for compundindexes to >> work, >> the first one has to be equality. But when i do execute explain on this >> query >> it does seem to use the index. So does this work? Should i disregard what >> ive >> read ststing that first field needs to be equality? >> >> I have this index actually. >> Index defined is >> >> (date, string2, string1) >> >> Select * from tbl_sample where date >= ? And date <= ? And string1 =?>> >> Thanks! >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf300faebbe7116e04b7fdaa1c

I don't have a version 10 available... But the manual is definitively wrong for V11.7. It's amazing to see the uses it can do on that kind of index... Naturally some of them are more efficient than others as Jonathan already explained. Regards. On Thu, Feb 2, 2012 at 4:27 PM, Fernando Nunes <domusonline@gmail.com>wrote: > I've found it, and it looks similar in V10 and V11.7. > I believe the manual is wrong (on V11.7 it does not even consider INDEX > SELF JOIN). > > It would be interesting to see the query plan for your query. > Regards > > On Thu, Feb 2, 2012 at 4:14 PM, Fernando Nunes <domusonline@gmail.com > >wrote: > > > Can you point the documentation you read? > > V10 infocenter start is here: > > > > http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp > > > > If you could find the part you read it could be useful to understand the > > situation. > > Regards. > > > > > > > > On Thu, Feb 2, 2012 at 4:11 PM, NATYURAL HORACIO < > > horacio.natyural@gmail.com> wrote: > > > >> Hi, > >> > >> I have this basic question regarding compound index usage. > >> I read on informix 10 guide on ibm website that for compundindexes to > >> work, > >> the first one has to be equality. But when i do execute explain on this > >> query > >> it does seem to use the index. So does this work? Should i disregard > what > >> ive > >> read ststing that first field needs to be equality? > >> > >> I have this index actually. > >> Index defined is > >> > >> (date, string2, string1) > >> > >> Select * from tbl_sample where date >= ? And date <= ? And string1 =?> >> > >> Thanks! > >> > >> > >> > >> > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --20cf300faebbe7116e04b7fdaa1c > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf300faebbb5b2da04b7fe0eba

i think manual is wrong. i have seen the explain plan showing that it does use index. i don't have version 10 available as well. they must have changed it.

The only indexes that require an equality filter on the leading column(s) is a Forest Of Trees (FOT) index. When you build an FOT index you specify which if the leading columns are used to create a hash and how many hash elements to create. The hash is used to create separate btree indexes one for each hash element value on those leading column(s). Normal btree indexes do not have this requirement. You can do range queries against any part of a btree index key. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 2, 2012 at 11:11 AM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > Hi, > > I have this basic question regarding compound index usage. > I read on informix 10 guide on ibm website that for compundindexes to work, > the first one has to be equality. But when i do execute explain on this > query > it does seem to use the index. So does this work? Should i disregard what > ive > read ststing that first field needs to be equality? > > I have this index actually. > Index defined is > > (date, string2, string1) > > Select * from tbl_sample where date >= ? And date <= ? And string1 =?> > Thanks! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f22c681c73be504b7fe7689

To help clarify the answer a little more. What do you mean by "work" is somewhat unclear. Using the index to position at an exact location, or scanning the index an using a filter to remove rows. Is your definition include both of these or just the first?? Second, during the life of version 10 there were some large improvements in this area when dealing with range predicates. I talked about them in several of the IIUG conferences when explaining index self-join and index performance improvements. We can utilize multiple levels of range predicates when setting the start and stop key of an index. If the engine was provided with the following index and query, it now can use date, string2 and string1 in the start and stop key. In early versions of 10, it would only use date in the start and stop key. (date, string2, string1) Select * from tbl_sample where date >= ? And date <= ? And string2 >= ? and string2 <= ? and string1 = ? John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) (Embedded image moved to file: pic48218.gif) ids-bounces@iiug.org wrote on 02/02/2012 08:11:33 AM: > From: "NATYURAL HORACIO" <horacio.natyural@gmail.com> > To: ids@iiug.org > Date: 02/02/2012 08:14 AM > Subject: Compound index usage. [26135] > Sent by: ids-bounces@iiug.org > > Hi, > > I have this basic question regarding compound index usage. > I read on informix 10 guide on ibm website that for compundindexes to work, > the first one has to be equality. But when i do execute explain on this query > it does seem to use the index. So does this work? Should i disregardwhat ive > read ststing that first field needs to be equality? > > I have this index actually. > Index defined is > > (date, string2, string1) > > Select * from tbl_sample where date >= ? And date <= ? And string1 =?> > Thanks! > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >