We have moved to GitHub Issues
Created by Shannon Deminick 14 Aug 2017, 05:51:27 Updated by Warren Buckley 03 Nov 2017, 14:15:39
Relates to: deploy-313
Subtask of: U4-9432
Currently the only way to query media by path is to use inefficient SQL queries. These queries use the nvarchar and ntext columns of the property table which aren't indexed and it also uses LIKE queries because most media now uses the image cropper which stores it's data as JSON. Therefore the combination of requiring a LIKE query in both the nvarchar and ntext fields (since it can either be an upload or image cropper property editor) means the query will be very slow and utilize a ton of SQL resources on the SQL server. Even if we were to add a DB index to the nvarchar field, it wouldn't solve the problem because a LIKE prefix query cannot use the index and also the image cropper property editor uses ntext which cannot be indexed.
We will create a new cmsMedia table which will be an extension table to the cmsContent table since this is media specific. It will contain an nvarchar field that is indexed purely to store the path of the media item so that we can query in an efficient way media by path regardless of the property editor used. The business logic will need to be updated to ensure that this table is populated. We might also utilize the logic we have in the 301 redirect system where we hash the value for faster lookups.
Would you still need the extra table if you solved U4-10041 by using UDI's in the URL?
If the media path was a UDI (well actually just a GUID) then we'd already have the GUID. That change is a much larger breaking change that we cannot 'just' do so this will still be required.
I fixed up the query being used to empty the recycle bin - that one would need to clear the row in
cmsMedia too, otherwise it would fail due to db constraints.
Other than that - I'm good with these changes.
I've merged but will leave in review until @warren.buckley is done testing :)
Been testing and works as expected. Items added to new DB correctly or updated & removed when media items are removed.
I did start the code review but with a crappy cold I've been a bit sloooow, @claus has beaten me to this & it's merged & all good to go :)
Type: Feature (request)
Backwards Compatible: True
Due in version: 7.8.0
Sprint: Sprint 71
Story Points: 3