JOIN on primary key (INT), WHERE by alphanumeric UID (VARCHAR)... Is it optimal?

Question

The goal is to change this URL...

app.com/journals/3/items

into

app.com/journals/aWNtFJXeYuvyhyJpqAjW/items

...and I was wondering if this is the optimal way to do it i.e. join on id, search by uid.

SELECT 
   ji.*,
   j.uid journalUid
FROM journalItems ji
   LEFT JOIN journal j ON j.id = ji.journalId
WHERE
   j.uid = 'aWNtFJXeYuvyhyJpqAjW'

journal
+----+----------------------+--------+
| id |    uid VARCHAR(20)   |  name  |
+----+----------------------+--------+
|  1 | cerGJ8cMKMK2njBtgsMy |   foo  |
|  2 | aWNtFJXeYuvyhyJpqAjW |   bar  |     <------- journal
|  3 | 7zwACt4fHkEchSjeeW7j |   baz  |      
+----+----------------------+--------+            

journalItems                                        
+----+----------------------+--------+-----------+ 
| id |    uid VARCHAR(20)   |  name  | journalId |
+----+----------------------+--------+-----------+ 
|  1 | ncv3VTlOMls6RPejJsjq |    a   |     1     | 
|  2 | NPz9CKEk7w14fKqIkCI2 |    b   |     1     |
|  3 | jghhnU29IKoOoG4smM4W |    c   |     1     |
|  4 | YE1nFNzjnusuyZeMNFWF |    d   |     2     |      <------- item
|  5 | 5N15UTgDgRjRKwt3yUCC |    e   |     2     |      <------- item
|  6 | 0eTBq3Ptzjbw32LkAQ0j |    f   |     3     |
+----+----------------------+--------+-----------+

Here's the UID generator:

function generateUid() {
    let length = 20;
    let uid = "";
    let characters = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
    for (let i = 0; i < length; i++) {
        uid += characters[Math.floor(Math.random() * characters.length)];
    }
    return uid;
}

Answer 1

Your where clause is undoing the left join, so you might as well use a regular join:

SELECT ji.*, j.uid as journalUid
FROM journalItems ji JOIN
     journal j
     ON j.id = ji.journalId
WHERE j.uid = 'aWNtFJXeYuvyhyJpqAjW';

For performance, you want indexes on journalItems(journalId) and on journal(uid, id).

Otherwise, your query is fine, although it seems odd that you want putting j.uid in the result set when you are only selecting one of them.