+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: Add-on Support (https://forum.kodi.tv/forumdisplay.php?fid=27)
+---- Forum: Picture Add-ons (https://forum.kodi.tv/forumdisplay.php?fid=150)
+---- Thread: Optimize MySQL for MyPictures Database AddOn (/showthread.php?tid=171054)
Optimize MySQL for MyPictures Database AddOn - human705 - 2013-08-11
Hi All,
Thanks for adding MySQL support for MyPictures Database. It works great for small libraries but mine is kinda big, a few thousand pictures. I was wondering if it is possible to create a few indexes and views to speed up the slideshow. I've added about 1200 pictures in my library and it takes about 5 minutes from the time i hit enter to the time the slide show begins.
If you are willing to share the table columns you need for the select statements, i can try to work on the views and store procedures so the code will do a simple select and all the processing will happen in the database.
Thanks again for a great addon
RE: Optimize MySQL for MyPictures Database AddOn - Xycl - 2013-08-12
The DB is not accessed when you start a slideshow from the skin. (left side in confluence skin)
Instead of the slideshow uses the list items which are already fetched from DB.
RE: Optimize MySQL for MyPictures Database AddOn - human705 - 2013-08-12
That is interesting. I have XBMC 12.2 running on Win7 and MySQL running on LINUX (OpenMediaVault). I'm using confluence skin and added the path to the sample pictures in windows (8 pictures in C:\Users\Public\Pictures\Sample Pictures). I setup "my Pictures Database" plugin to use MySQL using the instructions in the readme file. I've added Neor Profile SQL on the same Win7 machine to track the MySQL traffic. I then follow these steps: Pictures, Picture Add-ons, My Pictures Database, Browse by date and highlight 2008 (8 Pics). Hit the right arrow on the keyboard, select R. Slideshow and hit enter. According to the SQL profiler there are multiple sessions to the database (about 25), the worse 2 are executing 148 queries to return 32 rows.
What am I doing wrong?
RE: Optimize MySQL for MyPictures Database AddOn - Xycl - 2013-08-13
1) It doesn't matter what item is highlighted. The slideshow uses all items of the list.
2) Of course, recursive slideshow needs the entries of the the sub items which means that XBMC enters each sub item and MyPicsDB has to access the DB.
3) "Browse by date" has an item "All images from" and the items for the years which then have sub items for the months and these have sub items of the days within the months. Recursion means that all these entries are accessed!
If you want to get a slideshow from 2008 then go to "Browse by date->2008->All images from 2008". There you can start a slideshow.
If you start the slideshow from "Browse by date->2008" then you'll get all the pictures several times! A pictures from january 1st is in "All images from 2008", "All images from january 2008" and "Tuesday 01 January 2008".
RE: Optimize MySQL for MyPictures Database AddOn - human705 - 2013-08-13
Thanks for the clarification Xycl. This certainly reduces the number of times the database is accessed but on my setup with the 8 pictures and following the steps "Browse by date->2008->All images from 2008" I still have 148 queries (half of them are COMMIT statements) to return 32 rows. I think creating a couple of views in the database could reduce the 74 queries and improve performance. Running select statements with where clauses on tables and without keys is not recommended.
Is something like that on your road-map?
Thanks again.
RE: Optimize MySQL for MyPictures Database AddOn - Xycl - 2013-08-13
Beside the main select the following 9 selects are executed for each selected picture
Code:
SELECT ImageDateTime FROM Files WHERE strPath=? AND strFilename=?
SELECT Files.ImageRating FROM Files WHERE strPath=? AND strFilename=?
select tc.TagContent from TagTypes tt, TagContents tc, TagsInFiles tif, Files fi
where tt.TagType = 'GPS GPSLatitudeRef'
and tt.idTagType = tc.idTagType
and tc.idTagContent = tif.idTagContent
and tif.idFile = fi.idFile
and fi.strPath = ?
and fi.strFilename = ?
select tc.TagContent from TagTypes tt, TagContents tc, TagsInFiles tif, Files fi
where tt.TagType = 'GPS GPSLatitude'
and tt.idTagType = tc.idTagType
and tc.idTagContent = tif.idTagContent
and tif.idFile = fi.idFile
and fi.strPath = ?
and fi.strFilename = ?
select tc.TagContent from TagTypes tt, TagContents tc, TagsInFiles tif, Files fi
where tt.TagType = 'GPS GPSLongitudeRef'
and tt.idTagType = tc.idTagType
and tc.idTagContent = tif.idTagContent
and tif.idFile = fi.idFile
and fi.strPath = ?
and fi.strFilename = ?
select tc.TagContent from TagTypes tt, TagContents tc, TagsInFiles tif, Files fi
where tt.TagType = 'GPS GPSLongitude'
and tt.idTagType = tc.idTagType
and tc.idTagContent = tif.idTagContent
and tif.idFile = fi.idFile
and fi.strPath = ?
and fi.strFilename = ?
select coalesce(ImageDateTime, '0') from Files where strPath=? and strFilename=?
select coalesce(tc.TagContent,0) from TagTypes tt, TagContents tc, TagsInFiles tif, Files fi
where tt.TagType = 'EXIF ExifImageWidth'
and tt.idTagType = tc.idTagType
and tc.idTagContent = tif.idTagContent
and tif.idFile = fi.idFile
and fi.strPath = ?
and fi.strFilename = ?
select coalesce(tc.TagContent,0) from TagTypes tt, TagContents tc, TagsInFiles tif, Files fi
where tt.TagType = 'EXIF ExifImageLength'
and tt.idTagType = tc.idTagType
and tc.idTagContent = tif.idTagContent
and tif.idFile = fi.idFile
and fi.strPath = ?
and fi.strFilename = ?1) The first select for ImageDateTime is redundant due to 7th select for coalesce(ImageDateTime, '0').
This is a possible place for optimization.
2) The other selects can be concatenated into a big UNION select or instead of " tt.TagType =" you can use " tt.TagType in (...)".
Maybe you can get a better execution time, but maybe not.
3) I know that it would be better/easier to use the Files table primary key idFile instead of strPath & strFilename but this is part of the old orignal parameters handling done by the first developer. And I don't think that there woudl be any significant performance gain due to the unique key on Files(strPath, strFilename).
My calculation:
1) Main query
2) 8 pictures with 9 queries
= 73 queries.
4) I don't see any commit statements after a DB query in the source code. Therefore I don't know why you get commits in your log. Perhaps auto commit mode?
5) Why do you think that a view is faster, except a materialized view?
RE: Optimize MySQL for MyPictures Database AddOn - human705 - 2013-08-14
I was thinking that it may be better to push as much calculation as possible to MySQL. For example, create a stored procedure with the big union statement and have the result in a table or a view. Another way maybe to create a temporary table in a dataset and populate all the information you need for the selected images. This way a single select will return the results you need. Another option could be to create a table with the information you need to display (Top 50, browse by date, browse by path, etc.) at the time you import the images and avoid building them on the fly when a slideshow is requested.
I'm not familiar with python so i don't know if any of it is possible.
Thanks
RE: Optimize MySQL for MyPictures Database AddOn - Xycl - 2013-08-15
1) If you want to create a stored procedure, view, trigger or whatever then please keep in mind that it has to work with Sqlite, too.
That means instead of one general Python code you've to develop MySQL and Sqlite codes. And you have to test it!
2) Look in default.py for function "def show_pics(self):"
This is the main function which is responsible to show pics, export pics, zip pics for every condition (date, folders, tags) you can imagine.
There are a lot of calls like:
- MPDB.pics_for_period('year',self.args.value)
- MPDB.pics_for_period(self.args.period,self.args.value)
- MPDB.search_all_dates()
- MPDB.search_between_dates(DateStart=(self.args.datestart,formatstring), DateEnd=(self.args.dateend,formatstring))
- MPDB.filterwizard_result(self.args.kw.decode("utf8"), self.args.nkw.decode("utf8"), self.args.matchall, self.args.start, self.args.end)
- MPDB.search_tag(self.args.tag.decode("utf8"), self.args.tagtype.decode("utf8"))
All theses methods have to be amended to return additionally ImageDateTime, ImageRating, Width & Height and the GPS coordinates.
Afterwards you must change "def add_picture(self,picname,picpath,count=0, info="*",fanart=None,contextmenu=None,replacemenu=True):" to accept the additional parameters.
If you think that you can do it then please do it. I encourage you to optimize MyPicsDB.
But: I work with Oracle clusters. Most time we write PL/SQL packages which are called from C++.
The machines we have are really pretty fast due to tablespaces stored in SAN and 32 up to 64 CPU cores.
Though I know that it is not always clever to join several tables only because you want to reduce the number of the queries.
Sometimes it's better to load additionally data in the main fetch loop.
Therefore I think a reasonable approach is to reduce the queries in "add_pictures" to only one query.
RE: Optimize MySQL for MyPictures Database AddOn - Xycl - 2013-08-15
Additionally: You must not use temp. tables because plugins are terminated after returning the result to XBMC.
RE: Optimize MySQL for MyPictures Database AddOn - human705 - 2013-08-19
I'll give it a try and let you know.
Thanks
RE: Optimize MySQL for MyPictures Database AddOn - adila10 - 2019-01-22
Hi,
Did you try to clear the CMS cache?
this can probably solve the problem of the menu not working.
RE: Optimize MySQL for MyPictures Database AddOn - adila10 - 2019-01-23
(2019-01-22, 12:32)adila10 Wrote: Hi,Moreover: You should not utilize temp. tables in light of the fact that modules are ended in the wake of restoring the outcome to XBMC.
Did you try to clear the CMS cache?
this can probably solve the problem of the menu not working.
RE: Optimize MySQL for MyPictures Database AddOn - Klojum - 2019-01-23
(2019-01-22, 12:32)adila10 Wrote: Hi,Why are you responding to a 5.5 year old topic?