On Tue, 2004-03-02 at 00:49, [EMAIL PROTECTED] wrote: > what is the most performant way to select for example the first 99 > rows of a table and insert them into another table... > > at the moment i do this: > > for userrecord in select * from table where account_id = a_account_id > and counter_id = userrecord.counter_id and visitortable_id between > a_minid and a_maxid limit 99 loop
Using LIMIT without ORDER BY will give a selection that is dependent on the physical location of rows in the table; this will change whenever one of them is UPDATEd. > insert into lastusers (account_id, counter_id, date, ip, hostname) > values(a_account_id,userrecord.counter_id,userrecord.date > ,userrecord.ip,userrecord.hostname); > end loop; > > i think "limit" is a performance killer, is that right? but what to do > instead I'm sure it is the loop that is the killer. Use a query in the INSERT statement: INSERT INTO lastusers (account_id, counter_id, date, ip, hostname) SELECT * FROM table WHERE account_id = a_account_id AND counter_id = userrecord.counter_id AND visitortable_id between a_minid and a_maxid ORDER BY date DESC LIMIT 99; -- Oliver Elphick <[EMAIL PROTECTED]> LFIX Ltd ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match