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

[moltendorf]

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