Is this inefficient? (A little too much complexity.)
Question posted by: moltendorf
(Newbie)
on
August 25th, 2008 07: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)
- 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)
- 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)
- 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:
- 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:
- 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
0
Answers Posted
|
|
|
What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 197,031 network members.
|
