Feature proposal #7295
closed
Review implementing object database column
Added by krileon over 5 years ago.
Updated about 5 years ago.
Start date:
08 October 2018
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.
- Description updated (diff)
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.
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.
- 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.
Also available in: Atom
PDF
Updated by