Paging and Table Limits

Many YQL queries access datasources that contain thousands, or even millions, of items. When querying large datasources, applications need to page through the results to improve performance and usability. YQL enables applications to implement paging or limit table size at two levels: remote and local.

To find out how many items (rows) a query (SELECT) returns, in an XML response, check the value of the yahoo:count attribute of the query element. In a JSON response, check the value of the count object.

The maximum number of items returned by a SELECT is 5000. The maximum processing time for a YQL statement is 30 seconds. For most tables, the default number of items returned is 10. (That is, the default is 10 if you do not specify a limit in the SELECT statement.)

Remote Limits

A remote limit controls the number of items (rows) that YQL retrieves from the back-end datasource. To specify a remote limit, enter the offset (start position) and number of items in parentheses after the table name.

For example, in the following statement, the offset is 0 and the number of items is 10. When this statement runs, YQL calls Yahoo Search BOSS (the back-end source for the search.web table) and gets the first 10 items that match the query="pizza" filter:

select title from search.web(0,10) where query="pizza"

The following statement gets items 10 through 30. In other words, starting at postition 10, it gets 20 items:

select title from search.web(10,30) where query="pizza"

The default offset is 0. For example, the following statement gets the first 20 items:

select title from search.web(20) where query="pizza"

The default number of items for a remote limit varies with the table. For most tables, the default number of items is 10.

The maximum number of items also varies with table. To get the maximum number of items, enter 0 in parentheses after the table name. The following statement returns 1000 items from the search.web table:

select title from search.web(0) where query="pizza"

Local Limits

A local limit controls the number of rows YQL returns to the calling application. YQL applies a local limit to the data set that it has retrieved from the back-end datasource. To specify a local limit, include the LIMIT and OFFSET keywords (each followed by an integer) after the WHERE clause. LIMIT specifies the number of rows and OFFSET indicates the starting position. The OFFSET keyword is optional. The default offset is 0, which is the first row.

The following statement has a remote limit of 100 and a local limit of 15. When this statement runs, YQL gets up to 100 items from the back-end datasource. On these items, YQL applies the local limit and offset. This statement returns 15 rows to the calling application, starting with the first row (offset 0).

select title from search.web(100) where query="pizza" limit 15 offset 0

YQL retrieves items from the back-end datasource one page at a time until either the local or remote limit has been reached. The page size varies with the table. The following statement has an unbounded remote limit (0) so YQL retrieves items from the backend datasource until the the local limit of 65 is reached:

select title from search.web(0) where query="pizza" limit 65

Typically, a SELECT statement includes limits and filters, as shown in Example With Remote and Local Steps.

Table of Contents