Places/Places SQL queries best practices - Revision history

PeterMortensen: /* Perf hints */ Copy edited (e.g. ref. and ). Expanded.

2022-07-20T21:24:27Z

Perf hints: Copy edited (e.g. ref. <en.wiktionary.org/wiki/existence#Noun> and <en.wikipedia.org/wiki/Sentence_clause_structure#Run-on_sentences>). Expanded.

← Older revision		Revision as of 21:24, 20 July 2022
Line 258:		Line 258:
	</pre>		</pre>

	== ~~Perf~~ hints ==		== Performance hints ==
	* Use SQLite command <i>EXPLAIN QUERY PLAN statement</i> to check what tables and indices you're using, and try to optimize it.		* Use SQLite command <i>EXPLAIN QUERY PLAN statement</i> to check what tables and indices you're using, and try to optimize it.
	* When possible don't rely on SQLite internal optimizer~~, it~~ can do a very good job, but we can do also better, so always try to optimize your queries.		* When possible don't rely on SQLite internal optimizer. It can do a very good job, but we can do also better, so always try to optimize your queries.
	* Test queries against a big database~~, that~~ can make a lot of difference.		* Test queries against a big database. That can make a lot of difference.
	* Use <i>LIMIT</i> every time is possible, especially with partitioned tables, since when working without indices, having small intermediate tables is really important.		* Use <i>LIMIT</i> every time is possible, especially with partitioned tables, since when working without indices, having small intermediate tables is really important.
	* Use subqueries when you don't really need a complete JOIN but only check for ~~existance~~ of a record, or get at least one record of a certain type.		* Use subqueries when you don't really need a complete JOIN, but only check for existence of a record, or get at least one record of a certain type.
	* When a query is too slow, try rebuilding it to do the opposite and exclude results (<i>IN</i>, <i>NOT IN</i> constructs can be useful).		* When a query is too slow, try rebuilding it to do the opposite and exclude results (<i>IN</i>, <i>NOT IN</i> constructs can be useful).
	* Virtual machines have slower disk access, so take ~~that in count~~ when testing write ~~performances~~ in a vm.		* Virtual machines have slower disk access, so take take into account when testing write performance in a VM.
	* Use transactions every time you need to do multiple writes.		* Use transactions every time you need to do multiple writes.
	* Since each query can only use an index, the optimizer has to make a choice when the same query can be executed using two different indices. You can avoid using a certain index, and force the optimizer to use the another one, prepending the column name with a +. For example the following query will use the url index.<pre>SELECT id, url FROM moz_places WHERE +id = 5 AND url = 'http://www.mozilla.org/'</pre>		* Since each query can only use an index, the optimizer has to make a choice when the same query can be executed using two different indices. You can avoid using a certain index, and force the optimizer to use the another one, prepending the column name with a +. For example, the following query will use the 'url' index.<pre>SELECT id, url FROM moz_places WHERE +id = 5 AND url = 'http://www.mozilla.org/'</pre>

PeterMortensen: /* Querying Places database examples */ Copy edited (e.g. ref. and ). "information" in an uncountable noun in this context. Expanded.

2022-07-20T21:17:55Z

Querying Places database examples: Copy edited (e.g. ref. <en.wikipedia.org/wiki/C%2B%2B> and <en.wikipedia.org/wiki/JavaScript>). "information" in an uncountable noun in this context. Expanded.

← Older revision		Revision as of 21:17, 20 July 2022
Line 152:		Line 152:

	== Querying Places database examples ==		== Querying Places database examples ==
	This section contains an example of how to create a statement to query the Places database. This is for quick reference, further ~~informations are~~ available in the [https://developer.mozilla.org/en/Storage mozStorage developers documentation]		This section contains an example of how to create a statement to query the Places database. This is for quick reference, and further information is available in the [https://developer.mozilla.org/en/Storage mozStorage developers documentation].

	~~Cpp~~ code:		C++ code:
	<pre>		<pre>
	nsCOMPtr<mozIStorageStatement> statement;		nsCOMPtr<mozIStorageStatement> statement;
Line 176:		Line 176:
	</pre>		</pre>

	JS code:		JavaScript code:
	<pre>		<pre>
	var stmt = createStatement(		var stmt = createStatement(
Line 192:		Line 192:
	</pre>		</pre>

	Places is using some precompiled statement to speed up the most used queries creation. Those statements are created at Places init, so it's important to ~~choice~~ which queries need a precompiled statement, since every new addition can hit Ts. Precompiled statements can cause leaks if not correctly destroyed, so when creating a new one, remember to add it to the array in ::FinalizeStatements() method.		Places is using some precompiled statement to speed up the most used queries creation. Those statements are created at Places init, so it's important to choose which queries need a precompiled statement, since every new addition can hit Ts. Precompiled statements can cause leaks if not correctly destroyed, so when creating a new one, remember to add it to the array in ::FinalizeStatements() method.

	When querying with a precompiled statement it is important to use a mozStorageStatementScoper~~, it~~ will ensure the statement is correctly reset on scope exiting:		When querying with a precompiled statement it is important to use a mozStorageStatementScoper. It will ensure the statement is correctly reset on scope exiting:
	<pre>		<pre>
	{ // init of scope		{ // init of scope
Line 209:		Line 209:
	}		}
	} // end of scope		} // end of scope
	// precompiled statement gets ~~resetted~~ here		// precompiled statement gets reset here
	</pre>		</pre>


	~~in Javascript~~ instead, remember to use statement.reset() when you need to reuse a ~~stetement~~ changing ~~params~~, and statement.finalize() when you won't need it anymore.		In JavaScript instead, remember to use statement.reset() when you need to reuse a statement changing parameters, and statement.finalize() when you won't need it anymore.
	<pre>		<pre>
	var stmt = createStatement(		var stmt = createStatement(

PeterMortensen: /* Querying multiple tables */ Copy edited (e.g. ref. ). Expanded.

2022-07-20T21:10:03Z

Querying multiple tables: Copy edited (e.g. ref. <https://en.wiktionary.org/wiki/Cartesian_product#Noun>). Expanded.

← Older revision		Revision as of 21:10, 20 July 2022
Line 134:		Line 134:

	== Querying multiple tables ==		== Querying multiple tables ==
	When querying multiple tables involving foreign keys, please always avoid ~~cartesian~~ products like:		When querying multiple tables involving foreign keys, please always avoid Cartesian products like:
	<pre>		<pre>
	SELECT b.id, h.url		SELECT b.id, h.url
Line 146:		Line 146:
	JOIN moz_bookmarks b ON h.id = b.fk		JOIN moz_bookmarks b ON h.id = b.fk
	</pre>		</pre>
	That ensures the query is easier to expand, is less error-prone for future developers and more ~~perf-~~friendly.		That ensures the query is easier to expand, is less error-prone for future developers and more performance friendly.
	We actually use 2 types of <i>JOIN</i>s:		We actually use two types of <i>JOIN</i>s:
	* <i>LEFT JOIN</i> is used when the right table values could not exist, but we still want a record containing the left table values.		* <i>LEFT JOIN</i> is used when the right table values could not exist, but we still want a record containing the left table values.
	* <i>JOIN</i> is used when the right table must obey the <i>JOIN</i> condition, so records without a match in the right table won't be returned.		* <i>JOIN</i> is used when the right table must obey the <i>JOIN</i> condition, so records without a match in the right table won't be returned.

PeterMortensen: /* Table aliases */ Copy edited (e.g. ref. and ). Added the missing words, e.g., subjects. Expanded.

2022-07-20T21:07:35Z

Table aliases: Copy edited (e.g. ref. <https://en.wiktionary.org/wiki/complex#Adjective> and <https://en.wiktionary.org/wiki/conflict#Noun>). Added the missing words, e.g., subjects. Expanded.

← Older revision		Revision as of 21:07, 20 July 2022
Line 5:		Line 5:

	== Table aliases ==		== Table aliases ==
	When querying Places tables and in need of aliasing a table for joins or ~~complext~~ queries, is always better to use the same aliases in all queries.		When querying Places tables and in need of aliasing a table for joins or complex queries, it is always better to use the same aliases in all queries.

	This is needed not only for our internal code style, but also to avoid common ~~perf-~~killer paths in complex <i>DELETE</i>s or <i>UPDATE</i>s~~, indeed~~ referring to <i>table_name.column_name</i> in such a query could be different than referring to h.id, causing a full lookup of the table.		This is needed not only for our internal code style, but also to avoid common performance killer paths in complex <i>DELETE</i>s or <i>UPDATE</i>s. Indeed referring to <i>table_name.column_name</i> in such a query could be different than referring to h.id, causing a full lookup of the table.
	For the above reasons, we always try to avoid the <i>table_name.column_name</i> path.		For the above reasons, we always try to avoid the <i>table_name.column_name</i> path.

Line 41:		Line 41:
	</center>		</center>

	When joining a table with itself could make sense define an alias based on the <i>JOIN</i> meaning, for example selecting all bookmarks with a parent called "folder":		When joining a table with itself, it could make sense to define an alias based on the <i>JOIN</i> meaning, for example selecting all bookmarks with a parent called "folder":
	<pre>		<pre>
	SELECT b.id FROM moz_bookmarks b		SELECT b.id FROM moz_bookmarks b
Line 49:		Line 49:

	The code style for using aliases is:		The code style for using aliases is:
	* if the alias is on a real(~~temp~~) table from places.sqlite use <i>TABLE_NAME ALIAS</i>		* if the alias is in a real (temporary) table from places.sqlite, use <i>TABLE_NAME ALIAS</i>
	* if the alias is on a subquery use <i>AS</i> construct, like <i>SUBQUERY AS ALIAS</i>		* if the alias is in a subquery, use <i>AS</i> construct, like <i>SUBQUERY AS ALIAS</i>
	* when referring to a column of a table having an alias, always specify the alias, even if there'~~s no~~ risk of column names ~~conficts~~.		* when referring to a column of a table having an alias, always specify the alias, even if there isn't any risk of column names conflicts.

	== Querying the database in the temp tables era ==		== Querying the database in the temp tables era ==

Mak77: /* Querying the database in the temp tables era */

2009-02-10T15:22:25Z

Querying the database in the temp tables era

← Older revision		Revision as of 15:22, 10 February 2009
Line 58:		Line 58:

	NOTE: while for disk tables you can query <i>sqlite_master</i> table to get existant tables, indexes and triggers, for temporary tables you should instead use <i>sqlite_temp_master</i>. Notice mozStorage functions indexExists and tableExists are not correct atm ({{bug\|472963}}), so if you are looking for existance of moz_places_temp, they will return false even if the table exists.		NOTE: while for disk tables you can query <i>sqlite_master</i> table to get existant tables, indexes and triggers, for temporary tables you should instead use <i>sqlite_temp_master</i>. Notice mozStorage functions indexExists and tableExists are not correct atm ({{bug\|472963}}), so if you are looking for existance of moz_places_temp, they will return false even if the table exists.

			<b>WARNING: don't use any Places service on quit-application or later, since on quit-application we will close the database connection and data could be lost. Use quit-application-granted instead.</b>

	The tables are abstracted through a view, actual views are <i>moz_places_view</i> and <i>moz_historyvists_view</i>.		The tables are abstracted through a view, actual views are <i>moz_places_view</i> and <i>moz_historyvists_view</i>.

Mak77: /* Asynchronous statements */

2009-01-26T23:22:01Z

Asynchronous statements

← Older revision		Revision as of 23:22, 26 January 2009
Line 230:		Line 230:

	== Asynchronous statements ==		== Asynchronous statements ==
			Asynchronous statements are executed by mozStorage in a separate thread, and they are managed as a FIFO queue. Thanks to the separate threads using such a statement is a good idea, since it won't lock the UI. But at the same time we can't rely on immediate database values, since we have to wait for it to be executed (see [https://developer.mozilla.org/En/MozIStorageStatementCallback mozIStorageStatementCallback])

			To execute a single async statement in Cpp:
			<pre>
			nsCOMPtr<YourStatementCallback> callback =
			new YourStatementCallback();
			// this is needed to cancel a pending statement, we don't use it actually
			nsCOMPtr<mozIStoragePendingStatement> canceler;
			rv = stmt->ExecuteAsync(callback, getter_AddRefs(canceler));
			NS_ENSURE_SUCCESS(rv, rv);
			</pre>

			while in JS:
			<pre>
			var stmt = dbConn.createStatement("UPDATE ...")
			dbConn.executeAsync(stmt, mozIStorageStatementCallback_obj);
			</pre>

			It is also possible to execute multiple async statements at once using database connection executeAsync:
			<pre>
			let statements = [];
			statememnts.push(dbConn.createStatement("UPDATE ..."));
			statememnts.push(dbConn.createStatement("UPDATE ..."));
			dbConn.executeAsync(statements, statements.length, mozIStorageStatementCallback_obj);
			</pre>

	== Perf hints ==		== Perf hints ==

Mak77 at 22:58, 26 January 2009

2009-01-26T22:58:10Z

@@ Line 1: / Line 1: @@
-== Places SQL queries best practices ==
+= Places SQL queries best practices =
 This is a collection of suggestions and hints to help internal developers hack into the Places database correctly and with common code style.  Add-on developers are strongly recommended to use the Places API to query the database, since direct queries could end up corrupting the database when schema changes [https://developer.mozilla.org/Querying_Places Querying Places].
 For a better comprehension please look at the [https://wiki.mozilla.org/Places#Quick_Links Database schema ERD]
-=== Table aliases ===
+== Table aliases ==
 When querying Places tables and in need of aliasing a table for joins or complext queries, is always better to use the same aliases in all queries.
@@ Line 53: / Line 53: @@
 * when referring to a column of a table having an alias, always specify the alias, even if there's no risk of column names conficts.
-=== Querying the database in the temp tables era ===
+== Querying the database in the temp tables era ==
 Starting from Firefox 3.1 <i>moz_places</i> and <i>moz_historyvisits</i> tables are partitioned between disk and memory, to avoid common locking issues with OS's fsyncs calls ({{Bug|442967}}).
 New memory tables are called <i>moz_places_temp</i> and <i>moz_historyvisits_temp</i>, with the same columns and indexes as the disk tables.
@@ Line 131: / Line 131: @@
 This query gets the 10 most recent visit ids, this involves some code duplication, but is really faster than simply <i>UNION</i> all results when there are a lot of records.  Notice however in the external query we can't use column names, instead we have to use the column indices (2: eg visit_date).
-=== Querying multiple tables ===
+== Querying multiple tables ==
 When querying multiple tables involving foreign keys, please always avoid cartesian products like:
 <pre>
@@ Line 149: / Line 149: @@
 * <i>JOIN</i> is used when the right table must obey the <i>JOIN</i> condition, so records without a match in the right table won't be returned.
-=== Places database entry points ===
+== Querying Places database examples ==
-This section contains example queries for common tasks, notice most of this can already be done through APIs, but in some case toolkit code could directly query to avoid a perf hit.
+This section contains an example of how to create a statement to query the Places database. This is for quick reference, further informations are available in the [https://developer.mozilla.org/en/Storage mozStorage developers documentation]
-<i>Coming soon...</i>
+Cpp code:
-=== Perf hints ===
+== Perf hints ==
 * Use SQLite command <i>EXPLAIN QUERY PLAN statement</i> to check what tables and indices you're using, and try to optimize it.
 * When possible don't rely on SQLite internal optimizer, it can do a very good job, but we can do also better, so always try to optimize your queries.

Mak77: New page: == Places SQL queries best practices == This is a collection of suggestions and hints to help internal developers hack into the Places database correctly and with common code style. Add-o...

2009-01-26T22:04:31Z

New page: == Places SQL queries best practices == This is a collection of suggestions and hints to help internal developers hack into the Places database correctly and with common code style. Add-o...

New page

== Places SQL queries best practices ==
This is a collection of suggestions and hints to help internal developers hack into the Places database correctly and with common code style. Add-on developers are strongly recommended to use the Places API to query the database, since direct queries could end up corrupting the database when schema changes [https://developer.mozilla.org/Querying_Places Querying Places].

For a better comprehension please look at the [https://wiki.mozilla.org/Places#Quick_Links Database schema ERD]

=== Table aliases ===
When querying Places tables and in need of aliasing a table for joins or complext queries, is always better to use the same aliases in all queries.

This is needed not only for our internal code style, but also to avoid common perf-killer paths in complex DELETEs or UPDATEs, indeed referring to table_name.column_name in such a query could be different than referring to h.id, causing a full lookup of the table.
For the above reasons, we always try to avoid the table_name.column_name path.

<center>
{| border="1" cellpadding="2"
|+Commonly used table aliases
! Table || Alias || On-conflict alias
|-
| moz_anno_attributes || n ||
|-
| moz_annos || a ||
|-
| moz_bookmarks || b ||
|-
| moz_bookmarks_roots || r ||
|-
| moz_favicons || f ||
|-
| moz_historyvisits || v ||
|-
| moz_historyvisits_temp || v || v_t
|-
| moz_inputhistory || i ||
|-
| moz_items_annos || a || t
|-
| moz_keywords || k ||
|-
| moz_places || h || p
|-
| moz_places_temp || h || h_t
|}
</center>

When joining a table with itself could make sense define an alias based on the JOIN meaning, for example selecting all bookmarks with a parent called "folder":
<pre>
SELECT b.id FROM moz_bookmarks b
JOIN moz_bookmarks parents ON parents.id = b.parent
WHERE parents.title = 'folder'
</pre>

The code style for using aliases is:
* if the alias is on a real(temp) table from places.sqlite use TABLE_NAME ALIAS
* if the alias is on a subquery use AS construct, like SUBQUERY AS ALIAS
* when referring to a column of a table having an alias, always specify the alias, even if there's no risk of column names conficts.

=== Querying the database in the temp tables era ===
Starting from Firefox 3.1 moz_places and moz_historyvisits tables are partitioned between disk and memory, to avoid common locking issues with OS's fsyncs calls ({{Bug|442967}}).
New memory tables are called moz_places_temp and moz_historyvisits_temp, with the same columns and indexes as the disk tables.

NOTE: while for disk tables you can query sqlite_master table to get existant tables, indexes and triggers, for temporary tables you should instead use sqlite_temp_master. Notice mozStorage functions indexExists and tableExists are not correct atm ({{bug|472963}}), so if you are looking for existance of moz_places_temp, they will return false even if the table exists.

The tables are abstracted through a view, actual views are moz_places_view and moz_historyvists_view.
Since SQLite as of now (3.6.10) does not support indices on views, we have to follow two different paths based on the type of query we are executing:

* INSERTs/UPDATEs/DELETEs must always act on the views
** Real tables have triggers attached to them to allow for syncing so in no case an implementer should try to execute one of these operations on the temp tables.
** Views have triggers attached to them that ensure data are written/moved to memory tables when needed.
* SELECT must always act on real tables, since due to the lack of indices querying the view would be slow. We can SELECT from the views in tests though, when we are sure perf is not an issue.

So this is correct:
<pre>
DELETE FROM moz_places_view
WHERE id = 1337
</pre>
While this is completely WRONG:
<pre>
DELETE FROM moz_places_temp
WHERE id = 1337
</pre>

When using SELECT we must always be sure to query both tables, new changed data will be in the temp table, while the disk table will be synced in background or when a new bookmarks is added (to prevent bookmarks corruption). Still some place/visit won't never be synced to disk, for example visits with a visit_type = TRANSITION_EMBED.

To query both tables we can use a special SQL construct:
* UNION merges the resultsets of 2 queries discarding duplicates
* UNION ALL merges the resultsets of 2 queries

We use one of these based on the query, usually UNION is slower than UNION ALL, so the actual way we query both tables for a place with a certain url may be:
<pre>
SELECT id FROM moz_places_temp
WHERE url = 'http://www.mozilla.org'
UNION ALL
SELECT id FROM moz_places
WHERE url = 'http://www.mozilla.org'
AND id NOT IN(SELECT id FROM moz_places_temp)
</pre>

Due to the fact tables are partitioned, we cannot get the last inserted id from one of those, instead we should get the max between both tables, to do that we may, for example, execute:
<pre>
SELECT MAX(
(SELECT MAX(id) FROM moz_places_temp),
(SELECT MAX(id) FROM moz_places)
)
</pre>

A similar trick can be applied to IFNULL, this, in many complex queries, can help removing an expensive JOIN, to only get a single value:
<pre>
SELECT IFNULL(
(SELECT id FROM moz_places_temp WHERE url = 'http://www.mozilla.org'),
(SELECT id FROM moz_places WHERE url = 'http://www.mozilla.org')
) AS id
</pre>

Notice SQLite does support only one definition of GROUP BY, ORDER BY, LIMIT for UNIONs, but sometimes we don't need to UNION all results from both queries. In this case we can use a small trick:
<pre>
SELECT * FROM (
SELECT id, visit_date FROM moz_historyvisits_temp
WHERE visit_type = 1
ORDER BY visit_date DESC
LIMIT 10
)
UNION ALL
SELECT * FROM (
SELECT id, visit_date FROM moz_historyvisits
WHERE visit_type = 1
ORDER BY visit_date DESC
LIMIT 10
)
ORDER BY 2 DESC
LIMIT 10
</pre>

This query gets the 10 most recent visit ids, this involves some code duplication, but is really faster than simply UNION all results when there are a lot of records. Notice however in the external query we can't use column names, instead we have to use the column indices (2: eg visit_date).

=== Querying multiple tables ===
When querying multiple tables involving foreign keys, please always avoid cartesian products like:
<pre>
SELECT b.id, h.url
FROM moz_places h, moz_bookmarks b
WHERE h.id = b.fk
</pre>
Instead use a JOIN:
<pre>
SELECT b.id, h.url
FROM moz_places h
JOIN moz_bookmarks b ON h.id = b.fk
</pre>
That ensures the query is easier to expand, is less error-prone for future developers and more perf-friendly.
We actually use 2 types of JOINs:
* LEFT JOIN is used when the right table values could not exist, but we still want a record containing the left table values.
* JOIN is used when the right table must obey the JOIN condition, so records without a match in the right table won't be returned.

=== Places database entry points ===
This section contains example queries for common tasks, notice most of this can already be done through APIs, but in some case toolkit code could directly query to avoid a perf hit.

Coming soon...

=== Perf hints ===
* Use SQLite command EXPLAIN QUERY PLAN statement to check what tables and indices you're using, and try to optimize it.
* When possible don't rely on SQLite internal optimizer, it can do a very good job, but we can do also better, so always try to optimize your queries.
* Test queries against a big database, that can make a lot of difference.
* Use LIMIT every time is possible, especially with partitioned tables, since when working without indices, having small intermediate tables is really important.
* Use subqueries when you don't really need a complete JOIN but only check for existance of a record, or get at least one record of a certain type.
* When a query is too slow, try rebuilding it to do the opposite and exclude results (IN, NOT IN constructs can be useful).
* Virtual machines have slower disk access, so take that in count when testing write performances in a vm.
* Use transactions every time you need to do multiple writes.
* Since each query can only use an index, the optimizer has to make a choice when the same query can be executed using two different indices. You can avoid using a certain index, and force the optimizer to use the another one, prepending the column name with a +. For example the following query will use the url index.<pre>SELECT id, url FROM moz_places WHERE +id = 5 AND url = 'http://www.mozilla.org/'</pre>