Project

General

Profile

Actions

Feature proposal #7295

closed

Review implementing object database column

Added by krileon over 5 years ago. Updated almost 5 years ago.

Status:
Rejected
Priority:
High
Assignee:
Target version:
-
Start date:
08 October 2018
Due date:
% Done:

100%

Estimated time:

Description

This should store the ID of the source object. Examples as follows.

Asset: profile.57.registration
Object: 57

Asset: blog.79
Object: 79

Asset: groupjive.group.10.photo.20
Object: 20 (OR this could be 10 if we don't need the photo ID for any SQL compares)

Asset: groupjive.group.102.join
Object: 102

Asset: activity.132974.comment
Object: 132974

The idea is that it'll allow extending the queries to check for object access more efficiently and rebuilding of the object won't require a full parse of the asset to get the ID. This should be an int(11) column for efficient int comparison and lookup. Implement for Activity, Comments, and Notifications tables.

For a real world example we could check that the asset is a GJ photo relatively easily with a LIKE usage (not prefixed with %) which will work with index. Now that we know it's a photo we can grab compare the object to the GJ photo row and get the group id and check for access from there. If we don't need to do that we could just store the group id as Object and work from there. This removes the odd SUBSTRING_INDEX compares and allows int = int compares for significantly improved performance.

Actions #1

Updated by krileon over 5 years ago

  • Description updated (diff)
Actions #2

Updated by krileon over 5 years ago

Might also be useful to have target column. So for example you'd have the following structure.

FROM
id | user_id | asset | global | title | message | system | published | pinned | date
1 | 42 | groupjive.group.10.event.26 | 0 | scheduled an event | NULL | 0 | 1 | 0 | 2018-02-13 20:01:27

TO
id | user_id | asset | object | target | global | title | message | system | published | pinned | date
1 | 42 | groupjive.group.10.event.26 | 26 | 10 | 0 | scheduled an event | NULL | 0 | 1 | 0 | 2018-02-13 20:01:27

This would help with queries needing the source object and target object and better follows stream standards. Asset would still basically just be the location, which so far seams to work perfectly fine performance wise.

Actions #3

Updated by krileon almost 5 years ago

There needs to always be a target even if one isn't in the asset. So the above becomes the below.

Asset: profile.57.registration
Object: 57
Target: 57

Asset: blog.79
Object: 79
Target: 79

Asset: groupjive.group.10.photo.20
Object: 20
Target: 10

Asset: groupjive.group.102.join
Object: 102
Target: 102

Asset: activity.132974.comment
Object: 132974
Target: 132974

Asset: activity.132974.comment.56789
Object: 56789
Target: 132974

This parsing can be entirely automated. There isn't a need to really bother specifying these values manually. It will strictly work with IDs so grab the first id as the target and last id as the object.

Actions #4

Updated by krileon almost 5 years ago

  • Status changed from Assigned to Rejected
  • Target version deleted (5.0.0)
  • % Done changed from 0 to 100

This provided absolutely no performance benefit. The queries are already using indexes and SUBSTRING_INDEX already provides adequate performance. At most maybe CEIL can be added around the SUBSTRING_INDEX to guarantee it always does INT = INT compares.

Actions

Also available in: Atom PDF