Christian Haul dijo: > On 27.Jan.2003 -- 02:49 AM, Antonio Gallardo wrote: >> Niclas Hedhman dijo: >> > On Monday 27 January 2003 16:01, Torsten Curdt wrote: >> >> Niclas Hedhman wrote: >> >> > On Monday 27 January 2003 07:06, Torsten Curdt wrote: >> >> >>>What do the database to try to find the 6th row? >> >> >>> >> >> >>>I think the answer is: scan the rest of the database after >> finding >> >> the ONLY 5 rows that already exist. >> >> >> >> >> >>No :) ...be sure - it doesn't >> >> > >> >> > Well, that depends on the WHERE clause. For instance; >> >> > >> >> > WHERE SQRT( SQ( a - b ) + SQ( c - d ) + SQ( e - f ) ) < g ; >> >> > >> >> > definately go through every record, whether the fields are >> indexed >> >> or not. > > That's really bad! No way to refactor the schema or the query? Or use a > differen DBMS? E.g. in INFORMIX you can create an index based on a user > defined function... > >> I have a table that store the status of some tickets. You always know >> how many status there can be. If you said: >> >> 1-Open >> 2-Close >> 3-Invalid >> >> Then if you want to show the history, you will ask for LIMIT 3, but >> the database will try to find the 4th row after finding the only 3 >> that can exist. >> >> This is why I told this is a performance issue. Not an error. > > Again I believe an index would help a lot more. With bad luck your three > rows are spread all over the table and you're going to have a nearly > complete table scan anyways.
This is not bad luck. ;-) I cannot find an easy example. My queries envolves 5 or 7 tables. I have a status table. This table contains the date, ticket and status. This is because sometimes you search by status. I recently read that this is not a good idea indexing a field with small posibles values in a big table. That is my case. I am using Postgres, but in general this is true. Niclas pointed a example. I know this is not a good code, but the idea is there. The point is: I know there are only 5 row, but the LIMIT clausule will be written as: LIMIT 6. That will force to search the entire table for a row that never exists. > > And if it does improve performance, why do you want to use the > esql:limit tag instead of altering the SQL statement? How can I alter the statement? I can write LIMIT 5, but the code will rewrite it to LIMIT 6! This is the point. Sometimes we need to assure that the code write LIMIT 5 not LIMIT 6. This is why I suggested to just check if there is the <esql:more-results> tag. If the tag is there, that means that the programmer is not sure if there are or not more results. Please think about this issue. Best Regards, Antonio Gallardo. P.S: My english is very poor, sorry that I can not explain better this issue. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, email: [EMAIL PROTECTED]
