Jeff, Yes, unless the contents of the table have changed during the 'paging' sequence.
1. The SQL language specification explicitly doesn't guarantee a particular or reproducible order on rows returned for a select unless an ORDER BY clause is specified. How/whether order remains the same depends on the underlying implementation, in the case of MySQL, the underlying engines, whether they be MyISAM, InnoDB, MaxDB, heap, etc. MySQL engines will probably never change the order that it returns things in unless a table change occurs; a row is added/updated/deleted or the table is rebuilt/ checked/packed. 2. If a row is inserted in such a way that it would appear in a page that has already been displayed then it would not appear in a subsequent page display, but the last row displayed in the previous 'page' would be displayed again. 3. If a row that had been previously displayed is deleted, then the next select will be shy what would have been the first record that was to be displayed, and that record will never be displayed. Strategies to use for guaranteeing that all rows are displayed? First of all--always use an ORDER BY clause when doing these. 1. If your table is guaranteed not to change during the paging process, then you're good to go--all will be displayed. 2. If adds/deletes are possible during the process a. if you can guarantee that the paging sequence will be completed, LOCK the tables prior to beginning and _don't_ forget to UNLOCK them when done. b. If you can't guarantee the conditions for (a), then consider selecting the rows into a temporary table and doing the paging on that table instead of the original. --jim ------------------------------ > > Date: Sat, 09 Oct 2004 08:22:14 -0700 > From: Jeff Barr <[EMAIL PROTECTED]> > Subject: Will series of limited selects return entire table? > > If I have a MyIsam table that is not subject to modification, > is a series of select calls like this: > > select * from table limit 0,100; > select * from table limit 100,100; > select * from table limit 200,100; > ... > > Guaranteed to return each row in the table exactly once (as long > as I keep going until the end)? > > My limited testing seems to say that this is the case. However, > the documentation is silent on this issue. I would prefer not > to count on this if is not guaranteed. > > Jeff; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
