Yes here we go.
MySql has the limit function, Oracle has the 'rownum' variable and MsSQL has
cursors, blah
Definitely go with
the limit function. All the database servers have a viable method except
MsSQL!
Good
catch
Daniel
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Kevin Fricke
Sent: Wednesday, September 07, 2005 3:28 PM
To: [email protected]
Subject: RE: sql limithow about the limit function in mySQL?limit 0, 20Where you use variables for the values, such as:limit #startRow#, #maxRows#------Which would be faster? Pulling primary keys first, then the rows associated with specific primary keys or the limit function above?Thanks!Kevin-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Daniel Elmore
Sent: Wednesday, September 07, 2005 3:19 PM
To: [email protected]
Subject: RE: sql limitYes, the startRow and endRow are only query record pointers, you have still queried the full dataset. If your DB server supports cursors, you can use them to get rows from X to Y, but they are a pain to work with. I would suggest doing your full dataset query for primary keys only, then follow it with your data query grabbing only records with PKs from the desired range. However, with a 250 record window, your probably reaching the middle ground between equal performance with the standard approach.Daniel-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Kevin Fricke
Sent: Wednesday, September 07, 2005 3:07 PM
To: CFLIST
Subject: sql limitFirst of all, I assume that limiting the number of records will be much faster in the query than in the cfoutput, correct? If you do the 'ol startRow and endRow, isn't it pulling all of the records anyway.Second, if this is the case, what is the best way to handle the next, previous, etc. I ask because the table that I am pulling from has hundreds of thousands of records and I only want 250 at a time.
Thanks,Kevin
