>>> This is the example I tried to explain: >>> >>> 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. >> >> I sure hope you've normalized your database so that "status" is stored >> in a separate table from the rest of the history? If so, you should >> have a proper primary key in the history table that you can index on >> and the search will stop after looking at 4 records; the three that >> match and first one that doesn't match (at which point it will know >> the search is done.) >> > > I think this is not a issue of how I build my database or not. This is more general. Maybe this > improvement will does not help nothing in some Database constructions and give too much in > performance.
No, this is always a database design issue. > My point of view is: "If a user dont want to know if there are or not more results. We must almost > build the correct SQL clausule". In this case the correct clausule is: > > LIMIT X > > and not LIMIT X+1 In a properly designed database there should effectively be no difference between the two clauses. > Of course if you need to know if there are more rows, then the correct SQL clausule is: > > LIMIT X+1 > > This is without thinking if you use Oracle, PotgreSQL or any other DB Engine. I think that the > improvement will always helps. > > At the end, how much time take for a processor decide to use X or X+1 vrs. how much time will have > to search the X+1 row in a big table? I think the second one will be always slower regarless if you > index or not. Then lets help the database Engine with better SQL clausules. Tha will helps to build > a faster Database interface for Cocoon. ;-) In a properly designed database the improvement will be too small to measure.... Now, you might argue that we should not try and force the user to have a properly designed database. I agree that it would be nice, however, it is an impossible task: if you optimize for one bad design I can always find a counter example where your optimization introduces worse performance. In this particular case there is however another line of attack, and that it to look at having Cocoon use two different ways to stop the search. Each of these would be independent of each other and not interact. You would choose one or the other for any given query... --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, email: [EMAIL PROTECTED]
