Mailing-List: contact cocoon-dev-help@xml.apache.org; run by ezmlm
Precedence: bulk
Reply-To: cocoon-dev@xml.apache.org
Message-ID: <32965.10.0.0.1.1043686552.squirrel@ags01.agsoftware.dnsalias.com>
Date: Mon, 27 Jan 2003 10:55:52 -0600 (CST)
Subject: RE: [ESQL] Improvement....
From: "Antonio Gallardo" <agallardo@agsoftware.dnsalias.com>
To: <cocoon-dev@xml.apache.org>
In-Reply-To: <601F6322AD71D5118D6C0003472515290660D104@sjmemexc1.stjude.org>
References: <601F6322AD71D5118D6C0003472515290660D104@sjmemexc1.stjude.org>
Importance: Normal
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hunsberger, Peter dijo:
>>>> WHAT IS WRONG?
>>>>
>>>> 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:

http://techdocs.postgresql.org/techdocs/pgsqladventuresep3.php

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: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org