Hi Sheeri: Yes, you are misunderstanding my question. I certainly know how to limit my resultset to certain rows. I'm asking more about the effiencency of searching large volumes of data. Is making a search table like vBulletin does a good mechanism to avoid resource contention on the main table, or is that more work than what it's worth in my case?
-- Steve - Web Applications Developer http://www.sdwebsystems.com On Fri, May 5, 2006 2:35 pm, sheeri kritzer said: > Sounds like you want LIMIT and OFFSET -- > > everything after my name and before your post is copied from the doc at > > http://dev.mysql.com/doc/refman/4.1/en/select.html > > (or am I misunderstanding your question?) > -Sheeri > > The LIMIT clause can be used to constrain the number of rows returned > by the SELECT statement. LIMIT takes one or two numeric arguments, > which must both be non-negative integer constants (except when using > prepared statements). > > With two arguments, the first argument specifies the offset of the > first row to return, and the second specifies the maximum number of > rows to return. The offset of the initial row is 0 (not 1): > > SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15 > > To retrieve all rows from a certain offset up to the end of the result > set, you can use some large number for the second parameter. This > statement retrieves all rows from the 96th row to the last: > > SELECT * FROM tbl LIMIT 95,18446744073709551615; > > With one argument, the value specifies the number of rows to return > from the beginning of the result set: > > SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows > > In other words, LIMIT row_count is equivalent to LIMIT 0, row_count. > > For prepared statements, you can use placeholders (supported as of > MySQL version 5.0.7). The following statements will return one row > from the tbl table: > > SET @a=1; > PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?'; > EXECUTE STMT USING @a; > > The following statements will return the second to sixth row from the tbl > table: > > SET @skip=1; SET @numrows=5; > PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; > EXECUTE STMT USING @skip, @numrows; > > For compatibility with PostgreSQL, MySQL also supports the LIMIT > row_count OFFSET offset syntax. > > On 5/5/06, Steve <[EMAIL PROTECTED]> wrote: >> All: >> >> I am developing a search engine and using MySQL as the backend database >> management system. Under normal circumstances, when users search >> through >> large volumes of records on a search engine site, the results are broken >> down into pages. When a user clicks on the 'Next' link, the system will >> re-query the database and return those records that pertain to that page >> (records 10 through 20, perhaps). This, as you can plainly see, >> requires >> the application to re-query the same data from the same table each time >> the user clicks on a 'Next' or 'Previous' link. I would imagine that >> this >> may lead to some resource contention on that main table, especially when >> many users are using the system simultaneously. >> >> I've never seen vBulletin's code, but I have been able to determine from >> careful analysis (and testing) that they employ a search table, of >> sorts, >> that contains the returned records from a search. So, when a user of >> the >> system submits a search query, the system returns the records and throws >> them into a separate search table, identified by a sequential primary >> key >> field. Then, the system uses that search table to display the >> appropriate >> records according to the respective search identifier (referenced in the >> URL), never touching the main table again until a brand new search is >> performed. >> >> This seems to be a pretty good way to facilitate large text-based >> searches. Are there any other mechanisms that can be used to build a >> powerful, yet quick and light on resources, search system? Is a >> fulltext >> index the best way to achieve maximum performance with this kind of >> search? >> >> Thanks. >> >> -- >> Steve - Web Applications Developer >> http://www.sdwebsystems.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
