cocoon-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Antonio Gallardo" <>
Subject RE: [ESQL] Improvement....
Date Mon, 27 Jan 2003 16:55:52 GMT
Hunsberger, Peter dijo:
>>>> In example A all is OK.
>>>> In example B we are not using the power of the Database Manager.
>>>> WHY?
>>>> The LIMIT clause was designed to tell the database engine:
>>>> "Let find just X rows", then the database engine when it got the X
>>>> rows stop searching and return the X rows. It improves the response
>>>> time, since does not to continue searching!
>>>> Now think in a 10 million row table and YOU KNOW you need only 5
>>>> rows!
>>> whether 10 million rows or not - the current sollution will only ask
>>> for  6 - one additional row. Is that the time penalty you are talking
>>>  about?
>> Yes, because you already know that there are only 5 rows for every
> register. Then the database will > search the last 6th row that no
> exist. Forcing to searh in the ENTIRE table.
>> Maybe your 5 rows are at the beginning of the table but the non
>> existent
> 6th row will force the
>> Database Engine to search for this.
> Antonio,
> 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,

Antonio Gallardo.

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

View raw message