I'm getting lousy performance on string lookups

Hope someone can help ... Recently installed IDS on Linux w/kernel 2.0.31. here's my problem: I have a table of ~50,000 records, each with a uniquely indexed ID, and a NAME (char type). NAME is also indexed, with dups allowed. I can do lightning-fast lookups using the ID, but any searches using the name are very slow. Example: select * from table where name = "fred" (no rows returned) takes 20 seconds (I just timed it). I am just learning Informix, but have used Oracle for years, and I know that an indexed string field in Oracle will be looked up in just a few secs, for databases much bigger than this. I've made sure all pertinent fields are indexed. I would think the hash into the table would cause even non-unique indexed fields to be quickly located. If anyone could help me speed up these string lookups, I would greatly appreciate it. Email would be preferable. TIA -Mick -- Mick Oyer mick@cannon.net CannonNetSystems, Inc. http://www.cannon.net

Mick Oyer wrote: > > Hope someone can help ... > > Recently installed IDS on Linux w/kernel 2.0.31. here's my problem: > > I have a table of ~50,000 records, each with a uniquely indexed ID, > and a NAME (char type). NAME is also indexed, with dups allowed. > > I can do lightning-fast lookups using the ID, but any searches using > the name are very slow. > > Example: > > select * from table where name = "fred"> > (no rows returned) > > takes 20 seconds (I just timed it). I am just learning Informix, > but have used Oracle for years, and I know that an indexed string > field in Oracle will be looked up in just a few secs, for databases > much bigger than this. > > I've made sure all pertinent fields are indexed. I would think > the hash into the table would cause even non-unique indexed > fields to be quickly located. > > If anyone could help me speed up these string lookups, I would > greatly appreciate it. Email would be preferable. But you do not say if you have updated statistics! The Informix optimizer depends on the stats to make god decisions. If you run that name query with SET EXPLAIN ON, I think you will find that the query is doing a table scan because it does not know the probability of finding "fred" in that name column. Follow the recommendations for Update statistics in the release notes or get my dostats.ec program from the IIUG Software Repository in the package named utils2.ak which will perform the optimal set of stats commands (ie creating the most useful set of stats in the minimum time). Art S. Kagel