Re: How do I browse a database ?
Date: Sat, 10 Aug 2019 22:53:48 -0400
Message-ID: <qinvvl$9bg$1_at_jstuckle.eternal-september.org>
On 8/10/2019 12:00 PM, R.Wiesel wrote:
> Jerry,
>
>> Well, you've already ruled out the typical way it's done in MySQL - with >> "OFFSET" and "LIMIT".
>
> I did not rule it out - far from it actually - but as I'm told that that is
> /not/ the way to go (and I do understand the reasoning behind it) I'm
> (trying to) explore other methods.
>
>>> Currently I've got nothing ...
>
> By the way: I ment that in regard to a possible method (possibly) unique to
> MySQL you seemed to be hinting at.
>
>> The other problem you run into in creating a temporary table with just >> unique keys ...
>
> Thats another way. But as I started my quest on a low-resource machine (256
> MByte of memory) which could easily get exhausted by a "just the unique
> keys" temporary table I decided that I should not yet stop looking.
>
>> ...that if the database changes (rows added/deleted/updated) your >> temporary table is now out of date
>
> While mulling over the possibilities I realized that too, and noted it in
> the "con" column for that method. But here missing record would be
> visible, as a request for the record with a specific key cannot be completed
> (and the viewer displays an empty line).
>
> A similar problem exists for the OFFSET method, being that records
> disappearing below that "offset" causes the retrieval to "jump over" records
> that have not been displayed yet (but this happens without any kind of
> signalling)
>
>> What you do is add the primary key as the final sort parameter
>
> I'm not sure how, in a temporary database containing just the unique keys of
> the actual database, there should be 1) sorting 2) more than /only/ an
> unique key (record index?) of that temporary database as a parameter ...
>
> (confusing to have two "unique keys" to talk about. I hope the above is
> clear in which one is what.)
>
> Regards,
> Rudy Wieser
>
>
[Quoted] [Quoted] You don't use a temporary database. Just use the existing database. I [Quoted] [Quoted] hope you have a primary key on the table. You simply add the primary [Quoted] key as the last sort parameter and search for the next row based on the previous one.
For instance, your first search could be:
SELECT ... ORDER BY col1, col2, col3, keycol ASCENDING
Use the last values in col1, col2, col3 and the primary key in the next SELECT statement, i.e.
[Quoted] [Quoted] Now the next SELECT gets a little more complicated, but:
SELECT ... ORDER BY col1, col2, col3, primary_key ASCENDING
[Quoted] [Quoted] WHERE (col1>val1) OR (col1= val1 AND col2>VAL2) OR (col1=val1 AND col2= val2 AND col3>val3) OR (col1=val1 AND col2= val2 AND col3=val3 AND keycol> key1)
Add the columns you want retrieved and the LIMIT clause.
This still won't get rows added before the current selection criteria, but you won't be able to get those unless you retrieve the entire table or have a TIMESTAMP column indicating when the row was added or updated.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Sun Aug 11 2019 - 04:53:48 CEST