Many thanks for your reply Tom.
I will read it more closely over the week-end, but wanted to say thanks
straight away.
Kerry
-Original Message-
From: Tom Horstmann [mailto:[EMAIL PROTECTED]
Sent: 20 August 2004 15:52
To: 'Kerry Frater'; 'MySQL List'
Subject: RE: newbie question on scrolling through a table one record at
a time
Hi Kerry,
The problem is more fundamental with the scrolling through
the records/rows of Master. [..] It is not sensible to allow
200 million pieces of data to be transferred to the Delphi PC
to build a local Dataset to scroll through.
[..]
Getting the first row is easy
select * from Master order by MasterRef limit 1;
It is, but LIMIT mostly is executed after getting all records
meeting the WHERE-condition. That might be quite slow.
but getting the next record isn't as straight forward. Mainly
because I have no idea of what the next value of MasterRef
is. All I know is that I want to get the next row in
sequence. None of the papers I have or have seen addresses
this issue. Either this concept is not required in SQL
programming or it is so obvious that it doesn't need
explaining. Either way I can't see the wood for the trees.
If MasterRef is a unique value column then the next record would be:
select * from Master order by MasterRef limit 1 where
MasterRef MyCurrentMasterRefValue;
This simply raises questions 1) how to get the previous row
(presume you use the DESCENDING keyword of the table, 2) how
to test for Begining and End of Table and 3) what to do if
the column being ordered on is not unique.
I ran through the same problem the last days (still). This is
how i did it: At first you need a unique key (auto-increment).
I name it id. If you have records that are non-unique by the
column you want to sort them, try to add other columns to sort
on to get them as unique as you can. Having non-unique records
isn't a problem as long there are not many beeing equal.
How to move through records:
Starting with a value of '', always SELECT the record having a
higher value in that columns you sorted on than the last one.
As you said, that's all, if there all records are unique.
You will need LIMIT to get through equal records. It's quite
easy having an example:
record key
1a
2a
3b
4b
5c
Get the first record (assuming your id starts with 0):
SELECT key FROM table WHERE key='' AND id-1
ORDER BY key,id LIMIT 0,1
Store these:
old_key = key
old_id = id
Get the next record:
old_key = SELECT key FROM table WHERE key=old_key AND idold_id
ORDER BY key,id LIMIT 1,1
The LIMIT has to be 1 here to get the second record. As long as key
stays equal to old_key, increase the limit by one for each record
fetched. When getting a new value for key, set the limit to 1 again.
You need to find a good relation between the amount of equal records
and the columns you use to sort and move on. 500 records having the
same key will make things slow, but using a long WHERE- clause will
do, too.
I tested this for upto 3 records. If there is an index on all
columns used in the SELECTs, speed seems to be stable upto that size.
hth,
TomH
--
PROSOFT EDV-Loesungen GmbH Co. KGphone: +49 941 / 78 88 7 - 121
Ladehofstrasse 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0
Geschaeftsfuehrer: Axel-Wilhelm Wegmann [EMAIL PROTECTED]
AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
