cocoon-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Hunsberger, Peter" <>
Subject RE: [ESQL] Improvement....
Date Mon, 27 Jan 2003 17:56:34 GMT
>> There is only one search for which your 5 rows will be at the start of 
>> the table.  In general, you need an index on the table in order to 
>> find your data with any efficiency.  If you have an index that matches 
>> your search pattern then the search will stop after looking at 6 rows 
>> if it determines that the 6th row does not follow sequentially in the 
>> index after the other 5 rows.  There is no difference whether there 
>> are 10 or 10 million rows in the database.
> I recently read from a Postgres guru that is not good to index a field
with too few elements in 
> large table.
> For example, suppose you have a field with a boolean value. This is not a
good idea to index this
> field. This is a big penalization for every DB engine. Is better let him
to do the sequence search
> instead of a index search. And this sequence search is the penalization I
talk about.
> Also I did some benchmarks of this using postgres and really this makes a
diference for inserting,
> updating and deleting a row.....
> I think it is best to share all this info with you. please visit:
> There is a better description of when index and when not. from a database
guru, not me. ;-)
> Best regards,

Yes, fair enough, you're shouldn't be indexing on a type column (or a
Boolean column) in order to determine whether you've found your N records or
not.  Since there are, in general, never N records in this column it won't
tell you anything.  You still need a common foreign key somewhere else in
your database that you can index on. 

If you don't have such a key but you do have a database that supports hard
coded limits on search size then yes, you could improve performance but
having the hard limit.  In such a case, the average performance would be 50%
better for the hard stop (but you've moved hard coded business logic into
your SQL query which doesn't seem like a good idea to me).  However, the
whole issue goes away with proper database design...

To unsubscribe, e-mail:
For additional commands, email:

View raw message