Uploaded image for project: 'eZ Publish / Platform'
  1. eZ Publish / Platform
  2. EZP-16124

eZContentObjectTreeNode::createPermissionCheckingSQL() should define an index in temporary table

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 4.1.3, 4.1.4, 4.2.0
    • Fix Version/s: 4.1.5, 4.2.1, 4.3.0beta1
    • Component/s: Database related
    • Labels:
      None
    • Environment:

      Operating System: Debian 4.0
      PHP Version: 5.2.0
      Database and version: MySQL 5.0.32 & 5.1.43
      Browser (and version):

      Description

      When creating bigger websites with plenty of sections, tons of data and lots of registered users, the temp table created by eZContentObjectTreeNode::createPermissionCheckingSQL() tends to be rather slow as it defines no index which leads to a full table scan. This can be avoided by changing line 1599 from:

      $db->createTempTable( "CREATE TEMPORARY TABLE $groupPermTempTable ( user_id int )" );
      

      to

      $db->createTempTable( "CREATE TEMPORARY TABLE $groupPermTempTable ( user_id int(11) NOT NULL PRIMARY KEY )" );
      

      As you can see we have three improvements:
      1. 'int(11)' will lead to better performance when inserting data, as no type comparison/conversion must be performed by the SQL-Optimizer
      2. 'NOT NULL' speeds up all JOINs, as it avoids NULL-checking
      3. 'PRIMARY KEY' implicitely creates a unique index which will immensely improve joining speed

      We've tested this on a MySQL 5.0 and 5.1 Server and found that it reduced query execution time by ~20%

      I'm not sure, but my guess is that #014083 would be obsolete with these improvements.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                andre1 André R
                Reporter:
                Brains Jean-Bernard Valentaten
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: