Can I optimize the engine here or do I have to get the developers to change the SQL? ✓ Answered: green (solid confidence) — Classic Usenet correction pattern: Art Kagel's initial fix (append name_type as the SECOND column of the functional index) helps somewhat and is initially defended by Ian Gumby against a better suggestion, but Christian Knappke proposes and then empirically proves (est. cost 1 vs 282) that putting name_type FIRST is far better; Malc (the OP) confirms this order gives a much faster result, resolving the reported performance problem for the name_type='N' case.

Advisory only.

IDS 9.30HC5 running on HP-UX 11i. Engine upgrade is not an option, don't suggest it, see my previous posts going back some years! Here's the scenario: a table "cm" has 3.2million rows and we query it using something like (reduced for posting): SELECT<column list> FROM cm WHERE name_type = "N" AND upper60(<other_column>) MATCHES "LA*" The only bits that matter are the column "name_type", which is char(1) and unindexed, and "<other_column>", which an is indexed char(60) - the upper60() call is just a functional index we set up that helps on that column. A query using just "upper60(<other_column>) MATCHES "LA*"" is instant, as expected. If we include "name_type = "N"", it bogs and takes 1min45sec, at least. Here's sqexplain for each case: CASE 1: ======= SELECT<column list> FROM cm WHERE upper60(<other_column>) MATCHES "LA*" Estimated Cost: 655265 Estimated # of Rows Returned: 634086 1) dba.cm: INDEX PATH (1) Index Keys: dba.upper60(npname_name) (Serial, fragments: ALL) Lower Index Filter: dba.upper60(dba.cm.npname_name )MATCHES 'LA*' CASE 2: ======= SELECT<column list> FROM cm WHERE name_type = "N" AND upper60(<other_column>) MATCHES "LA*" Estimated Cost: 655265 Estimated # of Rows Returned: 2 1) dba.cm: INDEX PATH Filters: dba.cm.name_type = 'N' (1) Index Keys: dba.upper60(npname_name) (Serial, fragments: ALL) Lower Index Filter: dba.upper60(dba.cm.npname_name )MATCHES 'LA*' ...so the cost is the same and the only difference is the addition of the filter "cm.name_type= 'N'" Here's the histogram output for column "name_type": -- DISTRIBUTION --- ( N ) 1: ( 8569, 1071, P ) --- OVERFLOW --- 1: ( 3161859, P ) and the counts are: NULL - 4 rows " " - 8 rows "N" - 8193 rows "P" - 3,162,224 rows So a big skew. Update stats is up-to-date on the table (MEDIUM DISTRIBUTIONS ONLY followed by HIGH(column) on all index heads). The developers would like me to speed it up rather than have to change a bunch of SQLs that all do similar things. I've tried dropping the distributions on column "nake_type" and also the whole table, I've tried optimizing hints FIRST_ROWS, ALL_ROWS, FULL, AVOID_FULL; tried PDQ on and off and intermediate values for PDQPRIORITY, all sorts of things, nothing helps. I tried UPDATE STATISTICS HIGH on column (name_type), no help. I could include name_type in the index but with cardinality that skewed I don't see it helping. I think I can't improve it by engine manipulation - anyone else got a clue? Many thanks Malc

Append name_type to the functional index. So it would become: create index cm_func_new on cm( upper60(<othercolumn>), name_type ); That should do the trick. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. 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 Fri, Nov 6, 2009 at 7:51 AM, Malc <iiug@perrior.net> wrote: > IDS 9.30HC5 running on HP-UX 11i. Engine upgrade is not an option, > don't suggest it, see my previous posts going back some years! > > Here's the scenario: a table "cm" has 3.2million rows and we query it > using something like (reduced for posting): > SELECT<column list> FROM cm > WHERE > name_type = "N" > AND upper60(<other_column>) MATCHES "LA*" > > The only bits that matter are the column "name_type", which is char(1) > and unindexed, and "<other_column>", which an is indexed char(60) - > the upper60() call is just a functional index we set up that helps on > that column. > > A query using just "upper60(<other_column>) MATCHES "LA*"" is instant, > as expected. > > If we include "name_type = "N"", it bogs and takes 1min45sec, at > least. > Here's sqexplain for each case: > > CASE 1: > ======= > SELECT<column list> FROM cm > WHERE > upper60(<other_column>) MATCHES "LA*" > Estimated Cost: > 655265 > Estimated # of Rows Returned: > 634086 > > 1) dba.cm: INDEX > PATH > > (1) Index Keys: dba.upper60(npname_name) (Serial, fragments: > ALL) > Lower Index Filter: dba.upper60(dba.cm.npname_name )MATCHES > 'LA*' > > > CASE 2: > ======= > SELECT<column list> FROM cm > WHERE > name_type = "N" > AND upper60(<other_column>) MATCHES "LA*" > Estimated Cost: > 655265 > Estimated # of Rows Returned: > 2 > > 1) dba.cm: INDEX > PATH > > Filters: dba.cm.name_type = > 'N' > > (1) Index Keys: dba.upper60(npname_name) (Serial, fragments: > ALL) > Lower Index Filter: dba.upper60(dba.cm.npname_name )MATCHES > 'LA*' > > ...so the cost is the same and the only difference is the addition of > the filter "cm.name_type= 'N'" > > Here's the histogram output for column "name_type": > > -- DISTRIBUTION > --- > > ( N ) > 1: ( 8569, 1071, > P ) > > --- OVERFLOW > --- > 1: ( 3161859, P ) > > and the counts are: > NULL - 4 rows > " " - 8 rows > "N" - 8193 rows > "P" - 3,162,224 rows > > So a big skew. > > Update stats is up-to-date on the table (MEDIUM DISTRIBUTIONS ONLY > followed by HIGH(column) on all index heads). > The developers would like me to speed it up rather than have to change > a bunch of SQLs that all do similar things. I've tried dropping the > distributions on column "nake_type" and also the whole table, I've > tried optimizing hints FIRST_ROWS, ALL_ROWS, FULL, AVOID_FULL; tried > PDQ on and off and intermediate values for PDQPRIORITY, all sorts of > things, nothing helps. I tried UPDATE STATISTICS HIGH on column > (name_type), no help. I could include name_type in the index but with > cardinality that skewed I don't see it helping. I think I can't > improve it by engine manipulation - anyone else got a clue? > Many thanks > Malc > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Hello Malc, You probably know all this however...... i would at least try to create the index on create index ixie on cm(upper60(<other_col>),name_type) and see ifthat helps Without it the engine has to goto the datapage and see if it needs the record yes or no. Question i assume you fetch all the data needed in both cases??? eq dbaccess name_ofdb <<! select current from systables where tabname ='systables'; unload to /dev/null SELECT<column list> FROM cm WHERE name_type = "N" AND upper60(<other_column>) MATCHES "LA*" select current from systables where tabname ='systables';! dbaccess name_ofdb <<! select current from systables where tabname ='systables'; unload to /dev/null SELECT<column list> FROM cm WHEREupper60(<other_column>) MATCHES "LA*" select current from systables where tabname ='systables';! if both take the same amount of time, it then only takes longer to get a fist full of data... Superboer On 6 nov, 13:51, Malc <i...@perrior.net> wrote: > IDS 9.30HC5 running on HP-UX 11i. Engine upgrade is not an option, > don't suggest it, see my previous posts going back some years! > > Here's the scenario: a table "cm" has 3.2million rows and we query it > using something like (reduced for posting): > SELECT<column list> FROM cm > WHERE > name_type = "N" > AND upper60(<other_column>) MATCHES "LA*" > > The only bits that matter are the column "name_type", which is char(1) > and unindexed, and "<other_column>", which an is indexed char(60) - > the upper60() call is just a functional index we set up that helps on > that column. > > A query using just "upper60(<other_column>) MATCHES "LA*"" is instant, > as expected. > > If we include "name_type = "N"", it bogs and takes 1min45sec, at > least. > Here's sqexplain for each case: > > CASE 1: > ======= > SELECT<column list> FROM cm > WHERE > upper60(<other_column>) MATCHES "LA*" > Estimated Cost: > 655265 > Estimated # of Rows Returned: > 634086 > >   1) dba.cm: INDEX > PATH > >     (1) Index Keys: dba.upper60(npname_name)   (Serial, fragments: > ALL) >         Lower Index Filter: dba.upper60(dba.cm.npname_name )MATCHES > 'LA*' > > CASE 2: > ======= > SELECT<column list> FROM cm > WHERE > name_type = "N" > AND upper60(<other_column>) MATCHES "LA*" > Estimated Cost: > 655265 > Estimated # of Rows Returned: > 2 > >   1) dba.cm: INDEX > PATH > >         Filters: dba.cm.name_type = > 'N' > >     (1) Index Keys: dba.upper60(npname_name)   (Serial, fragments: > ALL) >         Lower Index Filter: dba.upper60(dba.cm.npname_name )MATCHES > 'LA*' > > ...so the cost is the same and the only difference is the addition of > the filter "cm.name_type= 'N'" > > Here's the histogram output for column "name_type": > > -- DISTRIBUTION > --- > > (                    N ) >  1: (    8569,     1071, > P ) > > --- OVERFLOW > --- >  1: ( 3161859,           P ) > > and the counts are: > NULL - 4 rows > " " - 8 rows > "N" - 8193 rows > "P" - 3,162,224 rows > > So a big skew. > > Update stats is up-to-date on the table (MEDIUM DISTRIBUTIONS ONLY > followed by HIGH(column) on all index heads). > The developers would like me to speed it up rather than have to change > a bunch of SQLs that all do similar things. I've tried dropping the > distributions on column "nake_type" and also the whole table, I've > tried optimizing hints FIRST_ROWS, ALL_ROWS, FULL, AVOID_FULL; tried > PDQ on and off and intermediate values for PDQPRIORITY, all sorts of > things, nothing helps. I tried UPDATE STATISTICS HIGH on column > (name_type), no help.  I could include name_type in the index but with > cardinality that skewed I don't see it helping. I think I can't > improve it by engine manipulation - anyone else got a clue? > Many thanks > Malc

Yup that does help - I was leaving that as I thought that the huge skew on that field's values would make it next to ineffective as an index key (especially when using the value "P" which is in the overflow bin). Obviously something I'm missing in my understanding of the optimizer, but hey...

Superboer said: "Without it the engine has to goto the datapage and see if it needs the record yes or no. " <fx: slaps forehead> ....Malc goes off to reread Samar Desai's excellent paper http://www3.software.ibm.com/ibmdl/pub/software/dw/dm/informix/0211desai/0211desai.pdf

Art Kagel wrote: > Append name_type to the functional index. Yes, that's what I'd suggest -- almost. > So it would become: > > create index cm_func_new on cm( upper60(<othercolumn>), name_type ); But I'd put name_type in the first place. Otherwise it would not help a lot. The query you mentioned will be handled differently: 1. (upper60(<othercolumn>), name_type) LA__________________________________________________________N 2. (name_type, upper60(<othercolumn>)) NLA__________________________________________________________ In the first case all rows where othercolumn starts with 'LA' need to be searches and then filtered for name_type = 'N', whereas in the second case only rows with name_type = 'N' need to be searched for othercolumn starting with 'LA'. Disclaimer: This optimization is only for the query SELECT <column list> FROM cm WHERE name_type = 'N' AND upper60(<other_column>) MATCHES 'LA*' If you leave out the name_type = 'N' part, you'll end with a table scan. So check all queries on the table and see if you need more than one index. HTH and best regards Christian

> From: chknews@gmx.net > Subject: Re: Can I optimize the engine here or do I have to get the developers to change the SQL? > Date: Fri, 6 Nov 2009 14:38:46 +0100 > To: informix-list@iiug.org > > Art Kagel wrote: > > So it would become: > > > > create index cm_func_new on cm( upper60(<othercolumn>), name_type );> > But I'd put name_type in the first place. Otherwise it would not help > a lot. The query you mentioned will be handled differently: > > 1. (upper60(<othercolumn>), name_type) > > LA__________________________________________________________N > > 2. (name_type, upper60(<othercolumn>)) > No. That would be a mistake. How unique is name_type? 52 possible single characters (not all will be used) over 20+ million rows? Errrr not good. Its worse when you realize that you may only use upper case (26 possible) and then you don't really use all 26 characters... Art is right in making it the second column to help with uniqueness in a single index. The key is to make sure that the first column of the index will give you the most unique set. Now if IDS allowed you to have multiple indexes on the same table and be used in a query... this wouldn't be a problem. (Is this out yet? or coming out in the next release?) It sounds like Malcolm has an issue where his index on the 'othercolumn' is returning more than 20K rows with LA as the first two characters. But hey! What do I know? I'm not a physical DBA. I just play one on TV. -G :-P _________________________________________________________________ Find the right PC with Windows 7 and Windows Live. http://www.microsoft.com/Windows/pc-scout/laptop-set-criteria.aspx?cbid=wl&filt=200,2400,10,19,1,3,1,7,50,650,2,12,0,1000&cat=1,2,3,4,5,6&brands=5,6,7,8,9,10,11,12,13,14,15,16&addf=4,5,9&ocid=PID24727::T:WLMTAGL:ON:WL:en-US:WWL_WIN_evergreen2:112009

Superboer wrote: > Hello Malc, > > You probably know all this however...... > i would at least try to create the index on > > create index ixie on cm(upper60(<other_col>),name_type) and see if> that helps > > Without it the engine has to goto the datapage and see if it needs the > record yes or no. > > Question i assume you fetch all the data needed in both cases??? > eq > > dbaccess name_ofdb <<! > select current from systables where tabname ='systables'; > unload to /dev/null > SELECT<column list> FROM cm > WHERE > name_type = "N" > AND upper60(<other_column>) MATCHES "LA*" > select current from systables where tabname ='systables';> ! > > > dbaccess name_ofdb <<! > select current from systables where tabname ='systables'; > unload to /dev/null > SELECT<column list> FROM cm > WHERE> upper60(<other_column>) MATCHES "LA*" > select current from systables where tabname ='systables';> ! > > > if both take the same amount of time, it then only takes longer to get > a fist full of data... > > Superboer > > On 6 nov, 13:51, Malc <i...@perrior.net> wrote: >> IDS 9.30HC5 running on HP-UX 11i. Engine upgrade is not an option, >> don't suggest it, see my previous posts going back some years! >> >> Here's the scenario: a table "cm" has 3.2million rows and we query it >> using something like (reduced for posting): >> SELECT<column list> FROM cm >> WHERE >> name_type = "N" >> AND upper60(<other_column>) MATCHES "LA*" >> >> The only bits that matter are the column "name_type", which is char(1) >> and unindexed, and "<other_column>", which an is indexed char(60) - >> the upper60() call is just a functional index we set up that helps on >> that column. >> >> A query using just "upper60(<other_column>) MATCHES "LA*"" is instant, >> as expected. >> >> If we include "name_type = "N"", it bogs and takes 1min45sec, at >> least. >> Here's sqexplain for each case: >> >> CASE 1: >> ======= >> SELECT<column list> FROM cm >> WHERE >> upper60(<other_column>) MATCHES "LA*" >> Estimated Cost: >> 655265 >> Estimated # of Rows Returned: >> 634086 >> >> 1) dba.cm: INDEX >> PATH >> >> (1) Index Keys: dba.upper60(npname_name) (Serial, fragments: >> ALL) >> Lower Index Filter: dba.upper60(dba.cm.npname_name )MATCHES >> 'LA*' >> >> CASE 2: >> ======= >> SELECT<column list> FROM cm >> WHERE >> name_type = "N" >> AND upper60(<other_column>) MATCHES "LA*" >> Estimated Cost: >> 655265 >> Estimated # of Rows Returned: >> 2 >> >> 1) dba.cm: INDEX >> PATH >> >> Filters: dba.cm.name_type = >> 'N' >> >> (1) Index Keys: dba.upper60(npname_name) (Serial, fragments: >> ALL) >> Lower Index Filter: dba.upper60(dba.cm.npname_name )MATCHES >> 'LA*' >> >> ...so the cost is the same and the only difference is the addition of >> the filter "cm.name_type= 'N'" >> >> Here's the histogram output for column "name_type": >> >> -- DISTRIBUTION >> --- >> >> ( N ) >> 1: ( 8569, 1071, >> P ) >> >> --- OVERFLOW >> --- >> 1: ( 3161859, P ) >> >> and the counts are: >> NULL - 4 rows >> " " - 8 rows >> "N" - 8193 rows >> "P" - 3,162,224 rows >> >> So a big skew. >> >> Update stats is up-to-date on the table (MEDIUM DISTRIBUTIONS ONLY >> followed by HIGH(column) on all index heads). >> The developers would like me to speed it up rather than have to change >> a bunch of SQLs that all do similar things. I've tried dropping the >> distributions on column "nake_type" and also the whole table, I've >> tried optimizing hints FIRST_ROWS, ALL_ROWS, FULL, AVOID_FULL; tried >> PDQ on and off and intermediate values for PDQPRIORITY, all sorts of >> things, nothing helps. I tried UPDATE STATISTICS HIGH on column >> (name_type), no help. I could include name_type in the index but with >> cardinality that skewed I don't see it helping. I think I can't >> improve it by engine manipulation - anyone else got a clue? >> Many thanks >> Malc > I was going to ask the same... It doesn't make too much sense that the query with name_type condition takes too much more time... The engine has to do the comparisons, but that should not be causing too much difference. Of course the first record can take longer... Regards.

Ian Michael Gumby wrote: > Now if IDS allowed you to have multiple indexes on the same table and be > used in a query... this wouldn't be a problem. (Is this out yet? or > coming out in the next release?) No. It's not out. It's one of the features of XPS that would make sense to port... we have to wait.

Well I know its coming. Its something I bothered Jerry K about just over a year. Just don't know where its in the pipeline. The only other 'must have' feature would be to create a column oriented data type (re: HBase). This shouldn't be too hard to build on. Something similar to the time series datablade. > From: domusonline@gmail.com > Subject: Re: Can I optimize the engine here or do I have to get the developers to change the SQL? > Date: Fri, 6 Nov 2009 22:29:58 +0000 > To: informix-list@iiug.org > > Ian Michael Gumby wrote: > > > Now if IDS allowed you to have multiple indexes on the same table and be > > used in a query... this wouldn't be a problem. (Is this out yet? or > > coming out in the next release?) > > No. It's not out. It's one of the features of XPS that would make sense > to port... we have to wait. > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Find the right PC with Windows 7 and Windows Live. http://www.microsoft.com/Windows/pc-scout/laptop-set-criteria.aspx?cbid=wl&filt=200,2400,10,19,1,3,1,7,50,650,2,12,0,1000&cat=1,2,3,4,5,6&brands=5,6,7,8,9,10,11,12,13,14,15,16&addf=4,5,9&ocid=PID24727::T:WLMTAGL:ON:WL:en-US:WWL_WIN_evergreen2:112009

Ian Michael Gumby wrote: >> Art Kagel wrote: > >> > So it would become: >> > >> > create index cm_func_new on cm( upper60(<othercolumn>), name_type );>> >> But I'd put name_type in the first place. Otherwise it would not help >> a lot. The query you mentioned will be handled differently: >> >> 1. (upper60(<othercolumn>), name_type) >> >> LA__________________________________________________________N >> >> 2. (name_type, upper60(<othercolumn>)) >> > No. > > That would be a mistake. > > How unique is name_type? 52 possible single characters (not all will be > used) over 20+ million rows? Errrr not good. Its worse when you realize > that you may only use upper case (26 possible) and then you don't really > use all 26 characters... According to the OP there are two possible values that count, N and P, with very few N (8193) and mostly P (3162224). Thus my disclaimer that this index helps with the query of the OP and not necessarily with any query. > Art is right in making it the second column to help with uniqueness in a > single index. At least it is not necessary to filter the data pages. But it needs to read a lot more of the index. If the first two letters of <othercolumn> are distributed evenly there are ~4700 rows that need to be checked if name_type is N > The key is to make sure that the first column of the index will give you > the most unique set. ... which is, in this case where name_type = 'N'. OK. I have tested it (IDS 11.50.TC5): > create table test( name_type char(1), other_column char(60)); Inserted ~3200000 rows with random data, 'P'/'N' = 8000 > create index test_1 on test (other_column, name_type); > update statistics high for table test; > set explain on; > select * from test where name_type = 'N' and other_column matches 'LA*'; > set explain off; yields: QUERY: (OPTIMIZATION TIMESTAMP: 11-09-2009 14:54:19) ------ select * from test where name_type = 'N' and other_column matches 'LA*' Estimated Cost: 282 Estimated # of Rows Returned: 1 1) informix.test: INDEX PATH (1) Index Name: informix.test_1 Index Keys: other_column name_type (Key-Only) Lower Index Filter: informix.test.other_column MATCHES 'LA*' Index Key Filters: (informix.test.name_type = 'N' ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 test type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 3 1 8869 00:00.01 283 > drop index test_1; > create index test_2 on test (name_type, other_column); > update statistics high for table test; > set explain on; > select * from test where name_type = 'N' and other_column matches 'LA*'; > set explain off; yields: QUERY: (OPTIMIZATION TIMESTAMP: 11-09-2009 14:56:55) ------ select * from test where name_type = 'N' and other_column matches 'LA*' Estimated Cost: 1 Estimated # of Rows Returned: 1 1) informix.test: INDEX PATH (1) Index Name: informix.test_2 Index Keys: name_type other_column (Key-Only) Lower Index Filter: (informix.test.name_type = 'N' AND informix.test.other_column MATCHES 'LA*' ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 test type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 3 1 3 00:00.00 1 See: different filtering, lower rows_scan, lower est_cost. Regards Christian

Thanks Christian - I was just composing the below ehen I read your response, much appreciated for the work you're putting in! Well... sorry this thread has gone offtopic (and I see it now carries the "SPAM?" tag, nice one) but I've got a far better result by putting the name_type field in the indexes as the first element, with the functional index field as the second element; the other way round (name_type as the second element) was still much slower. Of course, this only applies if the query includes a WHERE on a name_type value of "N" (8000 values out of 3M) but using "P" (most of the rest of the values) it's back to old speed so that's understandable. I *think* the issue is that the way I'd imagine it to work would be that (in the original case where the index DOESN'T include name_type but the query DOES) it finds the rows that match the "MATCHES LA*" criterion and then post-filters the result set on name_type; but this doesn't seem to be the way it works, it is in fact hellish slow whereas if I DON'T include name_type in the WHERE clause it's superspeed. So, I have a partial solution in that a query using name_type = N is much quicker but name_type = P is not. The obvious solution is to get the deveolpers to rewreite the query such that they retrieve all the rows and then postfilter the first 200 that match the name_type in code. (Oops didn't mention they have a "SELECT FIRST 200" at the top of the query). Trouble is, it's a web front end that allows users to specify their own value in the query form (the LA* is just an example, itcould be "MAC*" or "OBNOXIO*" or even just "*"). SO if some jerk just enters the "*" it could try to return 3 million rows, which is why there's a FIRST 200 in there. So post-filtering in code might timeout or might just not get enough of the required data back. Hmm

> From: iiug@perrior.net > Subject: Re: Can I optimize the engine here or do I have to get the developers to change the SQL? > Date: Mon, 9 Nov 2009 06:28:16 -0800 > To: informix-list@iiug.org > > Thanks Christian - I was just composing the below ehen I read your > response, much appreciated for the work you're putting in! > > Well... sorry this thread has gone offtopic (and I see it now carries > the "SPAM?" tag, nice one) but I've got a far better result by putting > the name_type field in the indexes as the first element, with the > functional index field as the second element; the other way round > (name_type as the second element) was still much slower. > Of course, this only applies if the query includes a WHERE on a > name_type value of "N" (8000 values out of 3M) but using "P" (most of > the rest of the values) it's back to old speed so that's > understandable. Yup. This is why you will want to put the column which will give you the most unique result first. You have to think about what your index will look like if you don't have uniqueness in the index. Remember your big 'O' math. (2 log(n)) I think? [Sorry its been 20+ years since my relational theory class.] When you get collisions in the index, you get 'trees'. I think that's the terminology someone used to described the linked list of similar results. (I want to say it was Fernando who mentioned it when talking about TPC-C benchmarking.) Looking at the fact that you can only have N or P as values, you'd want a binary index. But that doesn't help because you'll use your other index because it will give you a smaller subset to work with. > I *think* the issue is that the way I'd imagine it to work would be > that (in the original case where the index DOESN'T include name_type > but the query DOES) it finds the rows that match the "MATCHES LA*" > criterion and then post-filters the result set on name_type; but this > doesn't seem to be the way it works, it is in fact hellish slow > whereas if I DON'T include name_type in the WHERE clause it's > superspeed. > So, I have a partial solution in that a query using name_type = N is > much quicker but name_type = P is not. Yes. This is because by adding the second column to the index, you'll get a bit more uniqueness in the index for rows who have the value name_type = N. > The obvious solution is to get the deveolpers to rewreite the query > such that they retrieve all the rows and then postfilter the first 200 > that match the name_type in code. (Oops didn't mention they have a > "SELECT FIRST 200" at the top of the query). Trouble is, it's a web > front end that allows users to specify their own value in the query > form (the LA* is just an example, itcould be "MAC*" or "OBNOXIO*" or > even just "*"). SO if some jerk just enters the "*" it could try to > return 3 million rows, which is why there's a FIRST 200 in there. So > post-filtering in code might timeout or might just not get enough of > the required data back. > Hmm Errr, uhm, you've got a couple of problems... First, you need to make sure you're not open to SQL Injection. Second, what happens if the values that the individual wants isn't in the first 200 rows? I agree that post filtering makes sense, but what happens if you do a query and you post filter only to find that no rows matching were returned because you didn't specify the name_type = N ? (And because you have 3 mil rows with only 8000 rows that have name_type = N, its very likely that this scenario could happen. You could fetch 200 rows with name_type = P and post filtering yields no rows found. 8000 out of 3 million = 8/300,000 or ~ 1 in 37,500 rows fetched will match. So if you did a query... select * where ... MATCHES "*", the odds are roughly 1 in 100 that you find a query that contains name_type = N where you're limiting your result set to 200 tries. So you have to ask yourself... do you want a query that runs *fast* yet doesn't give you the results you want, or do you want a query that runs *slower* yet gives you the result set you asked for? But hey! What do I know? ;-) HTH -G _________________________________________________________________ Hotmail: Trusted email with powerful SPAM protection. http://clk.atdmt.com/GBL/go/177141665/direct/01/