Re^2: Ranking position in a SQL index

Just to clarify something here. The 'limit' clause has no effect on the efficiency of the search. It affects the overall efficiency of the query because it prevents you from accidentally requesting more rows than you can handle.

In the case where there is a unique index on the field (which is essentially what a PK-identity is anyway) the limit is entirely superlfuous and probably actually slows things down just marginally as its more to be marshalled accross the interprocess or intermachine barrier. Also to the best of my knowledge 'limit' is not standard SQL but rather a MySQL extension.

Anyway. ;-)

---
demerphq

_{First they ignore you, then they laugh at you, then they fight you, then you win.

-- Gandhi

Flux8}

Comment on Re^2: Ranking position in a SQL index

Replies are listed 'Best First'.
Re^3: Ranking position in a SQL index by dragonchild (Archbishop) on Oct 05, 2004 at 17:36 UTC
It is a MySQL extension, but an damn useful one and it's one of the things I consider when determining whether to use MySQL or Oracle for a given project. Oracle does have something similar, but it's harder to do. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re^4: Ranking position in a SQL index (vairants of limit:sybase) by demerphq (Chancellor) on Oct 05, 2004 at 17:42 UTC
Sybase does it via a variable called 'rowcount'. So you say `set rowcount 100 select * from foo` [download] But there is no way to do the equivelent of the skip feature of limit to the best of my knowledge. You'd have to manage it through ids and a where I think. I agree that 'limit' is one of a small set of things I like more about MySQL than Sybase. At least insofar as the MySQL that PM uses is the comparison I much prefer Sybase. :-) --- demerphq _{First they ignore you, then they laugh at you, then they fight you, then you win. -- Gandhi Flux8}	[reply] [d/l]
Re^5: Ranking position in a SQL index (vairants of limit:sybase) by mpeppler (Vicar) on Oct 05, 2004 at 19:35 UTC
The problem is that to implement "limit" in a truly relational system you really have to internally fetch all the rows before the first row specified in the limit, and then start returning rows. So you're going to do a lot of IOs if you have something like `select ... from big_table limit 1000000, 1000010` [download] And of course you should always include an ORDER BY clause as there is no guarantee that the result set will be int the "right" order. On the other hand if you use a WHERE clause the server can limit the IO to the correct pages of data... I have implemented versions of LIMIT by using a temporary table with an identity column, something like: `set rowcount 10010 select pk, id = syb_identity(10) into #tmp from big_table where some_c +ondition select b.* from big_table b, #tmp t where t.pk = b.pk and t.id >= 10000 set rowcount 0` [download] Obviously a lot of IO if you need to find rows located fairly deep into the result set, but acceptable for a pagination system where the user usually only looks at the first few pages... Michael	[reply] [d/l] [select]


XP is just a number
	PerlMonks