/ Check-in [b7e42ae7]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Rewrite a couple of queries used internally by FTS3 to take advantage of the "SELECT max(x) FROM ..." optimization.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b7e42ae77443b33e0ab83265064830594094dc7c
User & Date: dan 2010-03-19 13:59:19
Original User & Date: dan 2010-03-19 06:59:19
References
2010-03-26
17:42
Enhance FTS3 to take advantage of the MAX() optimization. Cherrypick of [b7e42ae774]. check-in: 4b65b480 user: drh tags: branch-3.6.23
Context
2010-03-19
15:48
Do not create, open, or initialize the rollback journal until something actually needs to be written into the journal. That way, expensive filesystem operations are avoided if the transaction ends up being a no-op. check-in: b78e58ae user: drh tags: trunk
13:59
Rewrite a couple of queries used internally by FTS3 to take advantage of the "SELECT max(x) FROM ..." optimization. check-in: b7e42ae7 user: dan tags: trunk
2010-03-18
16:34
Mark internal routines as "static" in FTS3. Ticket [b1552d0fa7]. check-in: 39b34ac4 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts3/fts3_write.c.

   182    182   /* 1  */  "SELECT NOT EXISTS(SELECT docid FROM %Q.'%q_content' WHERE rowid!=?)",
   183    183   /* 2  */  "DELETE FROM %Q.'%q_content'",
   184    184   /* 3  */  "DELETE FROM %Q.'%q_segments'",
   185    185   /* 4  */  "DELETE FROM %Q.'%q_segdir'",
   186    186   /* 5  */  "DELETE FROM %Q.'%q_docsize'",
   187    187   /* 6  */  "DELETE FROM %Q.'%q_stat'",
   188    188   /* 7  */  "SELECT * FROM %Q.'%q_content' WHERE rowid=?",
   189         -/* 8  */  "SELECT coalesce(max(idx)+1, 0) FROM %Q.'%q_segdir' WHERE level=?",
          189  +/* 8  */  "SELECT (SELECT max(idx) FROM %Q.'%q_segdir' WHERE level = ?) + 1",
   190    190   /* 9  */  "INSERT INTO %Q.'%q_segments'(blockid, block) VALUES(?, ?)",
   191         -/* 10 */  "SELECT coalesce(max(blockid)+1, 1) FROM %Q.'%q_segments'",
          191  +/* 10 */  "SELECT coalesce((SELECT max(blockid) FROM %Q.'%q_segments') + 1, 1)",
   192    192   /* 11 */  "INSERT INTO %Q.'%q_segdir' VALUES(?,?,?,?,?,?)",
   193    193   
   194    194             /* Return segments in order from oldest to newest.*/ 
   195    195   /* 12 */  "SELECT idx, start_block, leaves_end_block, end_block, root "
   196    196               "FROM %Q.'%q_segdir' WHERE level = ? ORDER BY idx ASC",
   197    197   /* 13 */  "SELECT idx, start_block, leaves_end_block, end_block, root "
   198    198               "FROM %Q.'%q_segdir' ORDER BY level DESC, idx ASC",