Re: Counting down
Date: Tue, 22 Aug 2017 17:45:43 -0400
Message-ID: <oni8di$n63$1_at_jstuckle.eternal-september.org>
On 8/22/2017 1:27 PM, John Levine wrote:
> In article <onhksv$ifv$1_at_jstuckle.eternal-september.org>,
> Jerry Stuckle <jstucklex_at_attglobal.net> wrote:
>> On 8/20/2017 11:46 PM, bit-naughty_at_hotmail.com wrote: >>> I have a DB, say 10 rows, with a primary key, numbered 1 to 10. ...
>
>> Forget TNP's "answer". It's his normal crap answer and only works if >> you have exactly 10 rows and the primary keys are 1-10.
>
> Well, yeah.
>
>> What you need is the LIMIT clause, i.e.
>
> It seems to me that we have a somewhat underspecified question. If you know
> that the records have a primary key and they are all in sequence, which is
> likely if it's an autoincrement primary key, fetching a record by key
> is the best approach. If you don't know the number of records, you can
> start by retrieving that and putting it in a local PHP variable, e.g.:
>
> SELECT MAX(keycol) FROM mytable
>
> and then
>
> sprintf "SELECT * FROM mytable where keycol=%d",keycolmax-n
>
> If all you know is that the keys are ascending but you don't know whether
> they're sequential, then SELECT ... ORDER BY keycol DESC n,1 is reasonable
> but it's considerably slower since it means mysql has to internally go
> through the index from the end to find the record rather than doing a
> direct lookup.
>
[Quoted] The order of rows in a table is unspecified unless you use the ORDER BY clause. And you CAN specify a key like you indicated - but only if the keys are contiguous - not something that is a good idea, because deleting a single row can cause the wrong row to be returned.
[Quoted] [Quoted] And ORDER BY ... DESC isn't as slow as you think. Since there is an index on the ORDER BY column (the primary key), MySQL can go X number of records back in the index - which would be even faster than searching for a specific key, even if it is a binary search.
Also, the SELECT MAX(keycol) requires an additional MySQL statement (more overhead), and unless you lock the table, an INSERT or DELETE in the table from another transaction can change that value and again provide incorrect results.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Tue Aug 22 2017 - 23:45:43 CEST