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: [EMAIL PROTECTED] For additional commands, email: [EMAIL PROTECTED]
