www

commit 3ff1ff88a910dd7aec603e9039fe7a9bd5332703
parent 33dedd17532f4ff3baa9896c565e0471729f415f
Author: Dan Stillman <dstillman@zotero.org>
Date:   Tue, 23 Jun 2015 14:50:17 -0400

A couple search query optimizations

...which make very little difference, but they look better in the SQLite
query plan view.

Diffstat:
M
chrome/content/zotero/xpcom/search.js
 | 
20
++++++--------------

1 file changed, 6 insertions(+), 14 deletions(-)
diff --git a/chrome/content/zotero/xpcom/search.js b/chrome/content/zotero/xpcom/search.js
@@ -888,7 +888,7 @@ Zotero.Search.idsToTempTable = function (ids) {
 	var tmpTable = "tmpSearchResults_" + Zotero.randomString(8);
 	
 	return Zotero.DB.executeTransaction(function* () {
-		var sql = "CREATE TEMPORARY TABLE " + tmpTable + " (itemID INT)";
+		var sql = "CREATE TEMPORARY TABLE " + tmpTable + " (itemID INTEGER PRIMARY KEY)";
 		yield Zotero.DB.queryAsync(sql);
 		
 		var ids2 = ids ? ids.concat() : [];
@@ -899,9 +899,6 @@ Zotero.Search.idsToTempTable = function (ids) {
 			yield Zotero.DB.queryAsync(sql, chunk, { debug: false });
 		}
 		
-		var sql = "CREATE INDEX " + tmpTable + "_itemID ON " + tmpTable + "(itemID)";
-		yield Zotero.DB.queryAsync(sql);
-		
 		return tmpTable;
 	});
 }
@@ -996,18 +993,13 @@ Zotero.Search.prototype._buildQuery = Zotero.Promise.coroutine(function* () {
 	}
 	
 	// Exclude deleted items (and their child items) by default
-	sql += " WHERE itemID " + (deleted ? "" : "NOT ") + "IN "
-			+ "("
-				+ "SELECT itemID FROM deletedItems "
-				+ "UNION "
-				+ "SELECT itemID FROM itemNotes "
+	sql += " WHERE itemID " + (deleted ? "" : "NOT ") + "IN (SELECT itemID FROM deletedItems) "
+		+ "AND itemID " + (deleted ? "" : "NOT ") + "IN (SELECT itemID FROM itemNotes "
 				+ "WHERE parentItemID IS NOT NULL AND "
-				+ "parentItemID IN (SELECT itemID FROM deletedItems) "
-				+ "UNION "
-				+ "SELECT itemID FROM itemAttachments "
+				+ "parentItemID IN (SELECT itemID FROM deletedItems)) "
+		+ "AND itemID " + (deleted ? "" : "NOT ") + "IN (SELECT itemID FROM itemAttachments "
 				+ "WHERE parentItemID IS NOT NULL AND "
-				+ "parentItemID IN (SELECT itemID FROM deletedItems) "
-			+ ")";
+				+ "parentItemID IN (SELECT itemID FROM deletedItems))";
 	
 	if (noChildren){
 		sql += " AND (itemID NOT IN (SELECT itemID FROM itemNotes "