Is this inefficient? (A little too much complexity.)

**moltendorf** · Aug 25 '08, 06:08 PM

Alright, I finally worked up a massive SQL query that gets me the result I want for my PHP 6 CMS: Module Engine Class.

I have read various comments within the dev.mysql.com documentation of MySQL 5.0 on JOIN, IN, and certain subqueries being much faster if done in a different form.

So even though I'm fairly sure this wont be much of a burden if one uses my CMS with a fairly normal amount of modules installed (maybe a couple hundred or so tops) I'm more concerned about someone who bogs down their install with a ton of modules installed (maybe a couple thousand modules). I'm not so sure how efficient this query would be on a set of 3 tables...

Table: modules (note I removed 'module_' from the column names)

Code:

identifier replaces parent super_parent name                      is_active is_primary directory
------------------------------------------------------------------------------------------------
1          0        0      1            Primary                   1         0          primary
2          0        0      2            Test Module News          1         1          test
3          0        0      0            Session Management        1         1          session
4          0        0      4            Auto Module 1             1         0          am1
5          0        0      5            Auto Module 2             1         0          am2
6          0        0      6            Auto Module 3             1         0          am3
7          0        6      6            Auto Module 3_Sub         1         0          am3s
8          5        0      5            Auto Module 2 Replacement 1         0          am2r

Table: modules_auto (note I removed 'auto_' from the column names)

Code:

module_identifier module_active sort
------------------------------------
4                 3             1
5                 3             2
6                 3             3

Table: modules_streams (note I removed 'stream_' from the column names)

Code:

identifier module_identifier sort
---------------------------------
1          1                 0
2          2                 0
3          3                 0
4          4                 0
5          5                 0
6          6                 1
6          7                 0
7          7                 0

SQL Query:

Code:

SET @current_identifier = 
	(
		SELECT `module`.`module_identifier`
			FROM `modules` `module`
			WHERE `module`.`module_directory` = IF
				(
					(
						SELECT COUNT(`module`.`module_identifier`)
							AS `total`
							FROM `modules` `module`
							WHERE
									`module`.`module_is_active` = '1'
								AND `module`.`module_is_primary` = '1'
								AND `module`.`module_directory` = 'session'
					) > (0),
					'session',
					'test'
				)
	);
SELECT
	`stream`.`stream_identifier`,
	`stream`.`stream_sort`,
	`module`.`module_identifier`,
	`module`.`module_replaces`,
	`module`.`module_name`,
	`module`.`module_directory`,
	`auto`.`auto_module_active`,
	`auto`.`auto_sort`
	FROM `modules_streams` `stream`
		LEFT JOIN `modules` `module`
			ON
				(
						`module`.`module_identifier` = `stream`.`stream_module_identifier`
					OR `module`.`module_replaces` = `stream`.`stream_module_identifier`
				)
		LEFT OUTER JOIN `modules_auto` `auto`
			ON
				(
						`module`.`module_identifier` = `auto`.`auto_module_identifier`
					OR `module`.`module_replaces` = `auto`.`auto_module_identifier`
				)
	WHERE `stream`.`stream_identifier` IN
		(
			SELECT `module`.`module_identifier`
				FROM `modules` `module`
					LEFT OUTER JOIN `modules_auto` `auto`
						ON `module`.`module_identifier` = `auto`.`auto_module_identifier`
				WHERE
						`module`.`module_identifier` = '1'
					OR `auto`.`auto_module_active` = '1'
					OR `module`.`module_identifier` = @current_identifier
					OR `auto`.`auto_module_active` = @current_identifier
		)
	ORDER BY
		`stream`.`stream_identifier` ASC,
		`stream`.`stream_sort` ASC;

Result Set:

Code:

stream_identifier stream_sort module_identifier module_replaces module_name               module_directory auto_module_active auto_sort
---------------------------------------------------------------------------------------------------------------------------------------
1                 0           1                 0               Primary                   primary          NULL               NULL
3                 0           3                 0               Session Management        session          NULL               NULL
4                 0           4                 0               Auto Module 1             am1              3                  1
5                 0           5                 0               Auto Module 2             am2              3                  2
5                 0           8                 5               Auto Module 2 Replacement am2r             3                  2
6                 0           7                 0               Auto Module 3_Sub         am3s             NULL               NULL
6                 1           6                 0               Auto Module 3             am3              3                  3