Re: Counting down
From: The Natural Philosopher <tnp_at_invalid.invalid>
Date: Tue, 22 Aug 2017 18:32:43 +0100
Message-ID: <onhpjp$2rn$2_at_dont-email.me>
>
>
> Well, yeah.
>
>
> 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.
>
Date: Tue, 22 Aug 2017 18:32:43 +0100
Message-ID: <onhpjp$2rn$2_at_dont-email.me>
On 22/08/17 18:27, 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.
>
Since that is what the poster specified, I resrticted myself to the simple case.
>> 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.
>
-- "It is an established fact to 97% confidence limits that left wing conspirators see right wing conspiracies everywhere"Received on Tue Aug 22 2017 - 19:32:43 CEST