in reply to Ranking position in a SQL index

SELECT idn FROM tablename WHERE index = 123 LIMIT 1;

Don't forget the LIMIT part. It makes the search more efficient. Note also that idn should be a primary key auto-increment, so it returns the row number assuming you don't delete any rows (better to flag rows for delete and then replace with fresh data later on).

On the other hand, if you're just doing this from a text file, you can set up a secondary file with all the index values and line numbers in order by index, and then perform a binary search to find the line number you want. Then the line number can be used to access the actual data in the other file.

Replies are listed 'Best First'.
Re^2: Ranking position in a SQL index
by demerphq (Chancellor) on Oct 05, 2004 at 17:29 UTC

    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


[reply]
      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]

        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]

In Section Seekers of Perl Wisdom