Re: query filter with calculated columns
--=====================_429770226==.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed
If c1 has a high selectivity (lots of different values - (unique ids have
high selectivity, columns like "gender" don't)), then the index on c1
should suffice.
If c1 does not have high selectivity (and if you're using Informix 9)
create a non-variant function F(a,b,c) = (a-b)+c
and create a functional index on F(c2,c3,c4).
Mike
At 07:30 PM 9/19/04, roger huang wrote:
>Hi all:
> I have a table t1 with c1,c2,c3,c4 columns.
>I have a query ,
>SELECT * FROM t1 where c1="a121" and (c2 -c3)+ c4 > 0.>To improve performance , do I need to create index on (c2,c3,c4)?
>Or can you tell me any way that I can improve it ?
----------------------------------------------
Michael Dunham-Wilkie, M.Sc., M.P.A.
Senior Database Analyst
Barrodale Computing Services Ltd.
Tel: (250) 472-4372 Fax: (250) 472-4373
Web: <http://www.barrodale.com>http://www.barrodale.com
Email: mike@barrodale.com
----------------------------------------------
Mailing Address:
P.O. Box 3075 STN CSC
Victoria BC Canada V8W 3W2
Shipping Address:
Hut R, McKenzie Avenue
University of Victoria
Victoria BC Canada V8W 3W2
----------------------------------------------
--=====================_429770226==.ALT
Content-Type: text/html; charset="us-ascii"
<html>
<body>
<br>
If c1 has a high selectivity (lots of different values - (unique ids have
high selectivity, columns like "gender" don't)), then the index
on c1 should suffice. <br><br>
If c1 does not have high selectivity (and if you're using Informix 9)
create a non-variant function F(a,b,c) = (a-b)+c<br>
and create a functional index on F(c2,c3,c4).<br><br>
Mike<br><br>
<br>
At 07:30 PM 9/19/04, roger huang wrote:<br><br>
<blockquote type=cite class=cite cite="">Hi all:<br>
I have a table t1 with c1,c2,c3,c4 columns.<br>
I have a query ,<br>
SELECT * FROM t1 where c1="a121" and (c2 -c3)+ c4 > 0.<br>To improve performance , do I need to create index on (c2,c3,c4)?<br>
Or can you tell me any way that I can improve it ?</blockquote>
<x-sigsep><p></x-sigsep>
<tt>----------------------------------------------<br>
Michael Dunham-Wilkie, M.Sc., M.P.A.<br>
Senior Database Analyst<br>
Barrodale Computing Services Ltd. <br>
Tel: (250) 472-4372 Fax: (250) 472-4373 <br>
Web:
<a href="http://www.barrodale.com"><font face="Courier New, Courier" color="#0000FF">http://www.barrodale.com<br>;
</a></font>Email: mike@barrodale.com <br>
----------------------------------------------<br>
Mailing Address:<br>
P.O. Box 3075 STN CSC<br>
Victoria BC Canada V8W 3W2 <br><br>
Shipping Address: <br>
Hut R, McKenzie Avenue <br>
University of Victoria <br>
Victoria BC Canada V8W 3W2<br>
---------------------------------------------- </body>
</html>
--=====================_429770226==.ALT--
sending to informix-list