U4-10286 - New cmsMedia database table to track media by media paths for fast lookups

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.

1 Attachments

Comments

Steve 20 Sep 2017, 09:00:07

Would you still need the extra table if you solved U4-10041 by using UDI's in the URL?

---

Shannon Deminick 21 Sep 2017, 05:37:18

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.

---

Shannon Deminick 30 Oct 2017, 12:23:53

https://github.com/umbraco/Umbraco-CMS/pull/2270

I have:

• Added new cmsMedia table which is implemented in the same type of way that cmsDocument is, if we ever did want to version media than this would support it
• Added an index to the PropertyType.Alias column
• Created migrations for this stuff along with a migration to populate the cmsMedia table with the correct media paths, the SQL to do that is the SQL used in Deploy/Courier for it's optimized get media calls (see image of after the upgrade)
• Updated the GetMediaByPath query to use the new table which uses the new indexes

---

Claus Jensen 03 Nov 2017, 13:18:39

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.

• The migration works and have confirmed it populates the table correctly.
• Confirmed the table is cleaned (null is set) when removing files from media items.
• Confirmed the table is updated when uploading a new file to a media item.
• Confirmed rows are removed from the table when deleting media items completely.
• Confirmed the media service uses the new table in the SQL generated when looking up items by path.

---

Claus Jensen 03 Nov 2017, 13:19:56

I've merged but will leave in review until @warren.buckley is done testing :)

---

Warren Buckley 03 Nov 2017, 14:15:26

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 :)

---