Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix the order of the LIMIT and OFFSET clauses in some examples on fts3.html (LIMIT comes before OFFSET). |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
d3fc079ba8c478390a00422db2dd62da |
User & Date: | dan 2010-12-29 04:52:18.000 |
Context
2011-01-05
| ||
21:46 | Fix a serious bug in the specification of the file format: the serial types for BLOB and TEXT where reversed. (check-in: 83a0bc6bb9 user: drh tags: trunk) | |
2010-12-29
| ||
04:52 | Fix the order of the LIMIT and OFFSET clauses in some examples on fts3.html (LIMIT comes before OFFSET). (check-in: d3fc079ba8 user: dan tags: trunk) | |
2010-12-17
| ||
17:49 | Add entries to the download.in page for snapshot shells for windows. (check-in: 02ac129c98 user: drh tags: trunk) | |
Changes
Changes to pages/fts3.in.
︙ | ︙ | |||
1789 1790 1791 1792 1793 1794 1795 | <i>-- that returns the number of space-separated integers contained in its only argument,</i> <i>-- the following query could be used to return the titles of the 10 documents that contain</i> <i>-- the greatest number of instances of the users query terms. Hopefully, these 10</i> <i>-- documents will be those that the users considers more or less the most "relevant".</i> SELECT title FROM documents WHERE documents MATCH <query> ORDER BY countintegers(offsets(document)) DESC | | | 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 | <i>-- that returns the number of space-separated integers contained in its only argument,</i> <i>-- the following query could be used to return the titles of the 10 documents that contain</i> <i>-- the greatest number of instances of the users query terms. Hopefully, these 10</i> <i>-- documents will be those that the users considers more or less the most "relevant".</i> SELECT title FROM documents WHERE documents MATCH <query> ORDER BY countintegers(offsets(document)) DESC LIMIT 10 OFFSET 0 </codeblock> <p> The query above could be made to run faster by using the FTS [matchinfo] function to determine the number of query term instances that appear in each result. The matchinfo function is much more efficient than the offsets function. Furthermore, the matchinfo function provides extra information |
︙ | ︙ | |||
1811 1812 1813 1814 1815 1816 1817 | <i>-- If the application supplies an SQLite user function called "rank" that</i> <i>-- interprets the blob of data returned by matchinfo and returns a numeric</i> <i>-- relevancy based on it, then the following SQL may be used to return the</i> <i>-- titles of the 10 most relevant documents in the dataset for a users query.</i> SELECT title FROM documents WHERE documents MATCH <query> ORDER BY rank(matchinfo(document)) DESC | | | 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 | <i>-- If the application supplies an SQLite user function called "rank" that</i> <i>-- interprets the blob of data returned by matchinfo and returns a numeric</i> <i>-- relevancy based on it, then the following SQL may be used to return the</i> <i>-- titles of the 10 most relevant documents in the dataset for a users query.</i> SELECT title FROM documents WHERE documents MATCH <query> ORDER BY rank(matchinfo(document)) DESC LIMIT 10 OFFSET 0 </codeblock> <p> The SQL query in the example above uses less CPU than the first example in this section, but still has a non-obvious performance problem. SQLite satisfies this query by retrieving the value of the "title" column and matchinfo data from the FTS module for every row matched by the users |
︙ | ︙ | |||
1845 1846 1847 1848 1849 1850 1851 | <codeblock> SELECT title FROM documents JOIN ( SELECT docid, rank(matchinfo(document)) AS rank FROM documents WHERE documents MATCH <query> ORDER BY rank DESC | | | 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 | <codeblock> SELECT title FROM documents JOIN ( SELECT docid, rank(matchinfo(document)) AS rank FROM documents WHERE documents MATCH <query> ORDER BY rank DESC LIMIT 10 OFFSET 0 ) AS ranktable USING(docid) ORDER BY ranktable.rank DESC </codeblock> <p> The next block of SQL enhances the query with solutions to two other problems that may arise in developing search applications using FTS: |
︙ | ︙ | |||
1896 1897 1898 1899 1900 1901 1902 | <i>-- implementation of the rank function has access to the static weight assigned</i> <i>-- to each document.</i> SELECT title, snippet(documents) FROM documents JOIN ( SELECT docid, rank(matchinfo(document), documents_data.weight) AS rank FROM documents JOIN documents_data USING(docid) WHERE documents MATCH <query> ORDER BY rank DESC | | | 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 | <i>-- implementation of the rank function has access to the static weight assigned</i> <i>-- to each document.</i> SELECT title, snippet(documents) FROM documents JOIN ( SELECT docid, rank(matchinfo(document), documents_data.weight) AS rank FROM documents JOIN documents_data USING(docid) WHERE documents MATCH <query> ORDER BY rank DESC LIMIT 10 OFFSET 0 ) AS ranktable USING(docid) WHERE documents MATCH <query> ORDER BY ranktable.rank DESC </codeblock> <p> All the example queries above return the ten most relevant query results. |
︙ | ︙ |