Does anyone know how to save a list of all unique keywords used in a Mylio installation to a text file? I have almost 700 unique keywords in use. Can it be done via the console? I’d like, outside of Mylio, to analyze the keywords used and perhaps rationalize/streamline my usage of them. Thanks for any help offered.
I had the same question in April and received a support email from Mathew on a number of questions. His response included the Mylio Console commands to list keywords and copy for pasting into a spreadsheet or other. It worked but my list wasn’t useful so I trashed it and didn’t keep the email. Hopefully he or someone will respond.
I can provide you a Mylio Console command that will list all of your keywords. Open the View Menu (3 dots in top right corner), then select
Console. Run the command
clear to clear anything currently in the console. Then, run this query:
sql select distinct MediaKeywords_Keyword from (WITH RECURSIVE split(MediaKeywords_id, MediaKeywords_Keyword, rest) AS (SELECT id, '', keywordsstr || ',' FROM Media WHERE id UNION ALL SELECT MediaKeywords_id, substr(rest, 0, instr(rest, ',')), substr(rest, instr(rest, ',')+1) FROM split WHERE rest <> '') SELECT MediaKeywords_id, MediaKeywords_Keyword FROM split WHERE MediaKeywords_Keyword <> '' ORDER BY MediaKeywords_id, MediaKeywords_Keyword) order by MediaKeywords_Keyword COLLATE NOCASE
Run the Console Command
copy to copy the contents of the Console to the computer clipboard. Paste into your text editor/word processor of choice.
This is very interesting. I have often wished we could sort the list of keywords in the Dashboard statistics area alphabetically.
So now I have run this SQL, and it gives me an odd collection of ‘extras’ at the end - for example, in the main list, I have a keyword ‘AgeAround1’ listed with a space in front of it; and at the end, in a shorter list sorted separately, ‘AgeAround1’ appears again with no preceding space. All the similar items in the second list are early in the alphabet, and I speculate that they are the ones that appear at the start of the keyword list in a number of images.
I have noticed in the past that from time to time, images exported from Mylio have a leading space on a keyword that duplicates a keyword later in the list without such a space. In Mylio, you can spot these because the keyword editor behaves as if there are multiple images with different keywords selected, even when there is only one selected. This shows up as a separate keyword in other apps (such as Apple Photos), and I long ago adapted my export processing script to remove the leading space where it is found in a keyword.
I wonder whether there is some inconsistency over the presence of a space at the start of keywords in Mylio’s database?
(I should add that the space is not present in the XMP files containing the AgeAround1 keyword, though it IS there for some other keywords.)
Oh sure, just your basic simple SQL query
Fantastic! That’s exactly what I needed. Top-class support from Mylio Support, as always - many thanks.
Interestingly, when I run the script I end up with an output situation similar to that detailed by aearenda above. Most keywords have a leading space, a smaller number do not. Most of the keywords are unique but a few are not i.e. they exist with a leading space and separately without a leading space. I’d prefer all keywords to be without a leading space - is it possible to achieve that easily, I wonder?
No odd ball keywords here; 760 total since I’ve been collecting photos for a while using various apps.
If I understand this which I only partly do, there is no list of keywords, the SQL is checking each file.
There is a table in the database that holds updated information about each image first gathered at import time, including the keywords, but as far as I know no separate table of keywords as such so far. It’s not reading each sidecar or image to get the keywords every time - that would be way too slow!
Update: With @Deon’s help I altered the SQL to try to avoid confusion when there are spaces in the keywords list - this seems to work better for me:
sql select distinct TRIM(MediaKeywords_Keyword) from (WITH RECURSIVE split(MediaKeywords_id, MediaKeywords_Keyword, rest) AS (SELECT id, '', keywordsstr || ',' FROM Media WHERE id UNION ALL SELECT MediaKeywords_id, substr(rest, 0, instr(rest, ',')), substr(rest, instr(rest, ',')+1) FROM split WHERE rest <> '') SELECT MediaKeywords_id, MediaKeywords_Keyword FROM split WHERE MediaKeywords_Keyword <> '' ORDER BY MediaKeywords_id, MediaKeywords_Keyword) order by TRIM(MediaKeywords_Keyword) COLLATE NOCASE
See SQL for list of keywords for more.
Extremely handy and shows (a) Mylio’s deep commitment to support and (b) the power of the underlying system. That said, it mystifies me why the Dashboard > Keywords function in the user interface doesn’t offer these capabilities to the average user (click to sort alphabetically, and copy to clipboard).
My big challenge – which this solution addresses – is mistakenly using multiple similar-sounding, but different, keywords for the same thing, and then not being easily able to figure out what I’ve done wrong. Thank you!