Reviewing & Optimizing Database Queries

19 views
Skip to first unread message

Dmitry A.

unread,
Mar 11, 2011, 11:08:58 AM3/11/11
to in-por...@googlegroups.com
Hello everyone,


We are starting a new discussion where we want to review, point out and optimize SQLs in In-Portal.


Please start listing your ideas in the following format:

==============
Problem: Incorrect INDEX used on ResourceId in CustomData type tables

SELECT %1$s.* %2$s FROM %1$s
LEFT JOIN '.TABLE_PREFIX.'%3$sPortalUserCustomData cust ON %1$s.ResourceId = cust.ResourceId

Proposed Solution: Change INDEX type to UNIQUE on ResourceId field in PortalUserCustomData table.

The same applies to all CustomData tables (ie. Links, Products, Categories, Articles, Topics)
==============

Please continue.



DA

er...@intechnic.lv

unread,
Mar 25, 2011, 10:46:39 AM3/25/11
to in-por...@googlegroups.com
Problem: incorrect joins and non-indexable filter, related to user groups.

SELECT PortalUser.* ,(g.Name) AS `PrimaryGroup`
FROM PortalUser
LEFT JOIN UserGroup ug ON PortalUser.PortalUserId = ug.PortalUserId AND ug.PrimaryGroup = 1
LEFT JOIN PortalGroup g ON ug.GroupId = g.GroupId
WHERE ((ug.GroupId <> 11 OR ug.GroupId IS NULL))

Proposed Solution:
Add indexed, not null default 0 field PrimaryGroupId to PortalUser table.
Remove ug.PrimaryGroup column.
In place of g.Name show options-formatted PrimaryGroupId field.
Change SQL query to much shorter and well indexed:

SELECT PortalUser.*
FROM PortalUser
WHERE PortalUser.PrimaryGroupId <> 11

Alexander Obuhovich

unread,
Mar 25, 2011, 10:57:49 AM3/25/11
to in-por...@googlegroups.com
In other words: move primary group mark from UserGroup table to PortalUser table.

Ok. Then associated code, that sets user's primary group should be changed. Also following checks should be made:
  • prevent deletion of group, that has members in it
  • prevent setting primary group field to group id, that user isn't a member of.
Then to show "primary indicator" in user group list we need to compare current group ID (that is being printed) with ID from user record.


However setting PrimaryGroupId field of PortalUser table to NOT NULL will violate the rule, that default field value must be among the ones, that can be stored in that field.
In this case 0 will represent Group with 0 ID, that can't exist. I prefer to use NULL value to indicate data absence in that field (e.g. user don't have a primary group yet).
 


On Fri, Mar 25, 2011 at 4:46 PM, er...@intechnic.lv <er...@intechnic.lv> wrote:
In place of g.Name show options-formatted PrimaryGroupId field



er...@intechnic.lv

unread,
Mar 25, 2011, 11:48:05 AM3/25/11
to in-por...@googlegroups.com, Alexander Obuhovich
0 means that user have no primary group. What advantage is from this NULL-rule in this case? With 0 we have more compact SQL query.

Alexander Obuhovich

unread,
Mar 25, 2011, 11:58:12 AM3/25/11
to in-por...@googlegroups.com
Yes, I know that NOT NULL approach was popular in Kernel3 (that is previous version of Framework, since Kenel4 is used for In-Portal), but empty value (NULL for integer fields) is more like no value, then 0.

Also no change in SQL needed to select user's that belong to a group, since JOIN to PortalGroup table by NULL value will to the same as JOIN by 0 value.

Guys what's your opinion on NULL/NOT NULL problem. Since NULL/NOT NULL is part of coding standard on Wiki, then I need opinions from other community members to make/not make a decision here.



On Fri, Mar 25, 2011 at 5:48 PM, er...@intechnic.lv <er...@intechnic.lv> wrote:
0 means that user have no primary group. What advantage is from this NULL-rule in this case? With 0 we have more compact SQL query.



er...@intechnic.lv

unread,
Mar 25, 2011, 12:43:01 PM3/25/11
to in-por...@googlegroups.com, Alexander Obuhovich
And what about selecting users that have no group? SELECT *  FROM `PortalUser` WHERE `PrimaryGroupId` = NULL will not work - so you can't use same SQL query template. SELECT *  FROM `PortalUser` WHERE `PrimaryGroupId` = 0 works good.

Dmitry A.

unread,
Mar 25, 2011, 1:45:12 PM3/25/11
to in-por...@googlegroups.com, Alexander Obuhovich
Hi Erik,


Actually it's "SELECT *  FROM `PortalUser` WHERE `PrimaryGroupId` IS NULL" and not the one listed.


Alex, Erik I have reviewed our "Programming Rules" and gave it another thought regarding using NULL setting.  In my opinion we should stick to using NULL for something as NO VALUE type fields which is suggested by MySQL.

In other words, NULL should be used as special condition and in our cases it means that value in PrimaryGroupId field has NOT been set yet.

Make sense?


DA

Alexander Obuhovich

unread,
Mar 26, 2011, 7:13:24 AM3/26/11
to in-por...@googlegroups.com
Yes.

er...@intechnic.lv

unread,
Mar 28, 2011, 11:17:44 AM3/28/11
to in-por...@googlegroups.com
I have not received an answer to the question - what is the practical reason of using NULL in this case?

I only see problems from such use. Suppose, in the users grid we have options filter on the field PromaryGroupId. If value in DB is 0, then we can easily and simply, using the standard options filter, show all users who have no primary group. If a DB value is NULL, you'll have to invent some clever filter for this case.

Where can I see these MySQL suggestions about NULL values?

Alexander Obuhovich

unread,
Mar 28, 2011, 11:54:12 AM3/28/11
to in-por...@googlegroups.com
I don't really understand your motives behind not liking NULL. Maybe you haven't used it to get a any database readability benefits of it.

I suppose you can use google yourself to find usage of NULL on MySQL website, but value absence is not the same as 0 for majority of people, who have participated in this discussion.

Dmitry A.

unread,
Mar 28, 2011, 11:59:27 AM3/28/11
to in-por...@googlegroups.com
Do we really going to have Users without a Primary Group?

I think it would be an error in the Registration or whatever process?


DA

Alexander Obuhovich

unread,
Mar 28, 2011, 12:07:04 PM3/28/11
to in-por...@googlegroups.com
You are right Dmitry. We can't have users without a primary group.

er...@intechnic.lv

unread,
Apr 1, 2011, 10:20:05 AM4/1/11
to in-por...@googlegroups.com
Since we can not have users without a primary group, then by the logic of the application this field is required and, accordingly, should appear as NOT NULL in the database structure.

Alexander Obuhovich

unread,
Apr 1, 2011, 10:58:24 AM4/1/11
to in-por...@googlegroups.com
To create a user you need to perform 2 insert statements:
  • one into PortalUser table
  • one into UserGroup table
  • update PortalUser table with GroupId inserted in previous insert succeeded

Since it's not an atomic operation by default, then we can't guarantee for 100% that no database table corruption or wrong database permissions will prevent correct insert statement into UserGroup  table.


On Fri, Apr 1, 2011 at 5:20 PM, er...@intechnic.lv <er...@intechnic.lv> wrote:
Since we can not have users without a primary group, then by the logic of the application this field is required and, accordingly, should appear as NOT NULL in the database structure.



Alexander Obuhovich

unread,
Apr 3, 2011, 12:17:35 PM4/3/11
to in-por...@googlegroups.com
Here is task for PrimaryGroupId field optimization: http://tracker.in-portal.org/view.php?id=1031
Reply all
Reply to author
Forward
0 new messages