Hierarchical vs Flat Keyword Lists and Performance in Lightroom

Question

As suggested by @John Thomas I posted this as a follow-up from: How many keywords are possible in Lightroom without a performance penalty?

Given the same total number of keywords, is there a performance difference btw a "flat" keyword list and a hierarchichal keyword list.

Answer 1

Short answer: It can be, depending on what you'll do.

Long answer: Usually, from database/storage point of view, the keywords engine is split in two (groups of) tables.

Now I'll take in consideration the simplest case with just two tables which will explain pretty clearly (IMHO) the phenomenon.

The first (group of) table(s) is the table which holds the keywords themselves. In this table can be a variable number of fields but for our purpose there are just the following fields:

• The unique keyword ID (table's primary key or PK for short)
• The parent's unique keyword ID (for geeks: a reference to the parent PK)
• (just for users) The keyword name (the text which will be drawn on the screen)

The important field here is the 2nd one which can have a special value ('null', 0, -1 etc.) if the said keyword doesn't have a parent.

As one can see here, when we speak about the keywords table there is NO difference if the list of keywords is flat or hierarchical..

Ok, if we want to be purists we can argue that a program which will support only flat keyword lists can be slightly faster because it won't have the 2nd field, but usually the lists of keywords are pretty small compared to the processing power of the modern computers, hence the difference will be negligible in this regard.

But there is a second table - the keyword links table - where the things get more complicated.

The Keyword Links table is the table in which are stored the links between each keyword and each photo to which is assigned.

In the simplest case this table has the following structure:

• Keyword ID (The Keyword's PK from the table above)
• Image ID (The Image's PK from the table of Images)

The big problem with this table is that it grows very quickly. For 100.000 photos (which is a normal-medium sized collection) putting only 4-7 keywords on each image (What, Where, When, Who at the bare minimum - also, Genre, Technique etc.) blows up the table to 400.000 - 700.000 records.

Theoretically, as you can see from the table's structure, there is no difference between using a flat or a hierarchical keywords' structure.

But practically it can be a difference, because in practice, the difference between theory and practice is much greater than in theory.

Concrete, for our discussion, there is a dangerous behavior in Lightroom when simultaneously with the assignment of a keyword to an image, the program regards that all the parent keywords are assigned with it.

Ok, if you know what you're doing, this can be good (that's why almost all programs have this) but otherwise it can virtually scale the Keyword Links table A LOT which can induce (and induces) - depending on what you do - performance problems.

Hence, (depending on your hardware) I advise to be careful with the Lightroom's hierarchy. While I think that a 'flat' list (even if the best solution in our discussion) is too constrained, I'd say to use hierarchical keywords but do not make the tree too deep.

As an aside, I don't see the cardinality of the branch to be a problem (IOW it doesn't make too much difference for the 'Root1' to have 100 or 1000 children) but, as I said, rather the nesting level.