Re: Index question

Posted in 2005

Cesae Cruz — Mon, 23 May 2005 12:08:32 -0500 (CDT) — source: Usenet: comp.databases.informix

--0-1131464060-1116868112=:30821 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi, How configuring SDK for Informix Dynamic Server 7.31. Regards, "Art S. Kagel" <kagel@bloomberg.net> escribi': Dirk Moolman wrote: It does not matter. The IDS optimizer uses costs to determine the best query plan. The order of the filters and join conditions in the WHERE and ON clauses do not affect the choices that the optimizer makes. You CAN affect the optimizer's decisions my maintaining sufficiently detailed statistics in the database by running UPDATE STATISTICS according to the recommendations in the Performance Guide (or as implemented in my dostats utility) and by providing appropriate indexes. To that know that IDS will only use ONE index per table so if you create singleton indexes on a, c, & d they will not be combined to filter this query, only the one providing the best filter value will be used. A composite index containing all three will help greatly though. Art S. Kagel > We have a new query, with a where-clause: > > where a and c = 0 (c can have values from 0 to 270) > and d = "2" (d can have 4 different values) > > > What is the best way to write this where clause, and which are the best > indexes to use ? > > The table has a total of 32 million records. > > > > > My own thoughts were: > > where d = "2" (get rid of most of the records) > and c = 0 (reduce the above subset to about 25%) > and a set) > > > And create indexes on c & d (which brings up another question - > composite or stand alone ?) > > > > sending to informix-list --------------------------------- Do You Yahoo!? Yahoo! Net: La mejor conexi'n a internet y 25MB extra a tu correo por $100 al mes. --0-1131464060-1116868112=:30821 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit <DIV>Hi,</DIV> <DIV> </DIV> <DIV>How configuring SDK for Informix Dynamic Server 7.31.</DIV> <DIV> </DIV> <DIV>Regards, "Art S. Kagel" <kagel@bloomberg.net> escribi':</DIV> <BLOCKQUOTE class=replbq style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">Dirk Moolman wrote: It does not matter. The IDS optimizer uses costs to determine the best query plan. The order of the filters and join conditions in the WHERE and ON clauses do not affect the choices that the optimizer makes. You CAN affect the optimizer's decisions my maintaining sufficiently detailed statistics in the database by running UPDATE STATISTICS according to the recommendations in the Performance Guide (or as implemented in my dostats utility) and by providing appropriate indexes. To that know that IDS will only use ONE index per table so if you create singleton indexes on a, c, & d they will not be combined to filter this query, only the one providing the best filter value will be used. A composite index containing all three will help greatly though. Art S. Kagel > We have a new query, with a where-clause: > > where a > and c = 0 (c can have values from 0 to 270) > and d = "2" (d can have 4 different values) > > > What is the best way to write this where clause, and which are the best > indexes to use ? > > The table has a total of 32 million records. > > > > > My own thoughts were: > > where d = "2" (get rid of most of the records) > and c = 0 (reduce the above subset to about 25%) > and a > set) > > > And create indexes on c & d (which brings up another question - > composite or stand alone ?) > > > > sending to informix-list </BLOCKQUOTE> <hr size=1>Do You Yahoo!? <a href="http://net.yahoo.com.mx">Yahoo! Net</a>: La mejor conexi'n a internet y 25MB extra a tu correo por <a href="http://net.yahoo.com.mx/">$100 al mes</a>. --0-1131464060-1116868112=:30821-- sending to informix-list

Re: Index question

Related threads

Your privacy choices