Details
-
Improvement
-
Resolution: Fixed
-
Medium
-
4.1.3, 4.1.4, 4.2.0
-
None
-
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
- is duplicated by
-
EZP-19155 Temporary table created in eZContentObjectTreeNode::createPermissionCheckingSQL() slows sites with many members
- Closed