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 1 a 2 a 3 b 4 b 5 c 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 id<>old_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 30000 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. KG phone: +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/68311 http://www.proSoft-Edv.de -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
