Development:Fast portable SQL - Revision history

Ddarjany: /* UPDATE with JOINs */

2008-01-16T17:25:46Z

UPDATE with JOINs

← Older revision		Revision as of 17:25, 16 January 2008
Line 14:		Line 14:

	* Pg and MySQL disagree on the extended syntax of UPDATE with JOIN		* Pg and MySQL disagree on the extended syntax of UPDATE with JOIN
	* MySQL does not support ~~to have~~ the table being updated in a subselect (but it supports it as JOINed table with a different alias - Pg does not support this)		* MySQL does not support having the table being updated in a subselect (but it supports it as JOINed table with a different alias - Pg does not support this)

	==Portable examples==		==Portable examples==

Ddarjany at 17:24, 16 January 2008

2008-01-16T17:24:24Z

← Older revision		Revision as of 17:24, 16 January 2008
Line 4:		Line 4:
	* Are scalable and fast		* Are scalable and fast

	A key strategy for scalability is to ensure we do as much work as possible in the DB with minimal back-and-forth with PHP. Modern DB engines have very smart optimizers and if we play to their ~~strenghts~~, can process millions of records in milliseconds. To hit those ~~strenghts~~ we want to reduce chattiness with the DB -- one "smart" SQL query (even if complex) is thousand of times better than a foreach loop in PHP that issues a thousand SQL queries. At least it will be more than a thousand times faster ;-)		A key strategy for scalability is to ensure we do as much work as possible in the DB with minimal back-and-forth with PHP. Modern DB engines have very smart optimizers and if we play to their strengths, can process millions of records in milliseconds. To hit those strengths we want to reduce chattiness with the DB -- one "smart" SQL query (even if complex) is thousand of times better than a foreach loop in PHP that issues a thousand SQL queries. At least it will be more than a thousand times faster ;-)

Martinlanghoff: /* Portable examples */

2008-01-14T20:44:52Z

Portable examples

← Older revision		Revision as of 20:44, 14 January 2008
Line 24:		Line 24:
	WHERE		WHERE
	NOT EXISTS (SELECT id FROM {$CFG->prefix}course_categories WHERE id={$CFG->prefix}course.category)		NOT EXISTS (SELECT id FROM {$CFG->prefix}course_categories WHERE id={$CFG->prefix}course.category)
	AND ~~mdl_course~~.id != $SITEID		AND {$CFG->prefix}course.id != $SITEID

	Delete chat_messages older than the (optional) keepdays setting in the related table chat. chat.keepdays the setting can be NULL or 0, both of which mean 'keep forever', so we have to be careful. This is somewhat clunky, but replaces hundreds of queries with 1. Notes...		Delete chat_messages older than the (optional) keepdays setting in the related table chat. chat.keepdays the setting can be NULL or 0, both of which mean 'keep forever', so we have to be careful. This is somewhat clunky, but replaces hundreds of queries with 1. Notes...

Fox: Little correction (revert it if i'm wrong)

2008-01-14T09:12:05Z

Little correction (revert it if i'm wrong)

← Older revision		Revision as of 09:12, 14 January 2008
Line 35:		Line 35:
	* The subselect works on MySQL because it does not refer to the table we are deleting from		* The subselect works on MySQL because it does not refer to the table we are deleting from

	$~~keepdays~~ = "SELECT c.keepdays		$subselect = "SELECT c.keepdays
	FROM {$CFG->prefix}chat c		FROM {$CFG->prefix}chat c
	WHERE c.id = {$CFG->prefix}chat_messages.chatid";		WHERE c.id = {$CFG->prefix}chat_messages.chatid";
	$sql = "DELETE		$sql = "DELETE
	FROM {$CFG->prefix}chat_messages		FROM {$CFG->prefix}chat_messages
	WHERE ($subselect) > 0 AND timestamp < ( ".time()." -($subselect) * 24 * 3600)";		WHERE ($subselect) > 0 AND timestamp < ( ".time()." -($subselect) * 24 * 3600)";

Martinlanghoff: /* UPDATE with JOINs */

2008-01-14T05:41:02Z

UPDATE with JOINs

← Older revision		Revision as of 05:41, 14 January 2008
Line 25:		Line 25:
	NOT EXISTS (SELECT id FROM {$CFG->prefix}course_categories WHERE id={$CFG->prefix}course.category)		NOT EXISTS (SELECT id FROM {$CFG->prefix}course_categories WHERE id={$CFG->prefix}course.category)
	AND mdl_course.id != $SITEID		AND mdl_course.id != $SITEID

			Delete chat_messages older than the (optional) keepdays setting in the related table chat. chat.keepdays the setting can be NULL or 0, both of which mean 'keep forever', so we have to be careful. This is somewhat clunky, but replaces hundreds of queries with 1. Notes...

			* If the subselect returns NULL or 0 it won't satisfy '>0' - so we handle both cases in one check
			* We substract $keepdays - 24 * 3600 ... if we add it we can sometimes exceed the boundaries of an int in PostgreSQL
			* Could be improved to run the subselect only once
			** Is there any DB that won't optimize this?
			** How can we do this portably?
			* The subselect works on MySQL because it does not refer to the table we are deleting from

			$keepdays = "SELECT c.keepdays
			FROM {$CFG->prefix}chat c
			WHERE c.id = {$CFG->prefix}chat_messages.chatid";
			$sql = "DELETE
			FROM {$CFG->prefix}chat_messages
			WHERE ($subselect) > 0 AND timestamp < ( ".time()." -($subselect) * 24 * 3600)";

Martinlanghoff at 05:10, 14 January 2008

2008-01-14T05:10:13Z

New page

This is a work in progress! We keep notes here of bits of SQL that we know that

* Work on all supported DBs (this is tricky!)
* Are scalable and fast

A key strategy for scalability is to ensure we do as much work as possible in the DB with minimal back-and-forth with PHP. Modern DB engines have very smart optimizers and if we play to their strenghts, can process millions of records in milliseconds. To hit those strenghts we want to reduce chattiness with the DB -- one "smart" SQL query (even if complex) is thousand of times better than a foreach loop in PHP that issues a thousand SQL queries. At least it will be more than a thousand times faster ;-)

=UPDATE with JOINs=

A good scalable way to update a lot of data is to combine the UPDATE with a SUBSELECT, to UPDATE the data directly from the DB itself. The rows never need to go back and forth to PHP, so this is extremely fast. The problem is that it is very hard to get it portable across DBs.

Problems

* Pg and MySQL disagree on the extended syntax of UPDATE with JOIN
* MySQL does not support to have the table being updated in a subselect (but it supports it as JOINed table with a different alias - Pg does not support this)

==Portable examples==

Fix a broken relation - this is roughly equivalent to a subselect with a LEFT OUTER JOIN:

UPDATE {$CFG->prefix}course
SET category = $defaultcategory
WHERE
NOT EXISTS (SELECT id FROM {$CFG->prefix}course_categories WHERE id={$CFG->prefix}course.category)
AND mdl_course.id != $SITEID