Reviewing & Optimizing Database Queries

[Dmitry A.]

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]

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]

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

--
Best Regards,

http://www.in-portal.com
http://www.alex-time.com

[er...@intechnic.lv]

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]

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]

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.]

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]

Yes.

[er...@intechnic.lv]

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]

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.]

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]

You are right Dmitry. We can't have users without a primary group.

[er...@intechnic.lv]

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]

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]

Here is task for PrimaryGroupId field optimization: http://tracker.in-portal.org/view.php?id=1031