RE: Optimize A Simple Query -- Help

Chao Try creating an index on table (col1, col2, key). This should make the = optimizer utilize the index path. Also since col1 and col2 are highly = duplicate values, this will give the index some degree of uniquenss so = the optimizer finds it worth giving the new index a try. Hope this helps Sujit Pal DBA, Computer Sciences Corp. ---------- From: Chao Y. Din[SMTP:cdin@csc.com] Sent: Monday, September 16, 1996 6:29 AM To: informix-list@rmy.emory.edu Subject: Optimize A Simple Query -- Help I have a simple select count statement that takes three minutes to execute, yet I only have about 300,0000 rows. Here is the SQL. SELECT COUNT(*) FROM table WHERE key[1,7] =3D "B02800A" AND col1 =3D =2010 AND col2 =3D 10; The "key" field is the primary key with data type char(11). "col1" and "col2" have a composite index. Values of the key field are evenly distributed, but col1 and col2 are not. The number of possible value of col1 and col2 do not exceed 50. Col1 and col2 have indexes just because there are reports searching on these two columns. If I drop the col1 and col2 clauses, the query will utilize index and hence run fast. But combining the substring and the composite key, the query is no longer using the "key" field for index search. Here is the output from SET EXPLAIN ON. Estimated Cost: 3 Estimated # of Rows Returned: 1 1) table: INDEX PATH Filters: table.key[1,7] =3D "B02800A" (1) Index Keys: col1 col2 Lower Index Filter: (table.col1 =3D 2010 AND table.col2 =3D 10) Is it possible to force the query to use index on key without dropping the composite index? Thanks in advance. Chao