Get row id on update

Question

Let's say I have a table of the following structure:

| name        | Type          |
| ----------- |:-------------:|
| id          | primary       |
| word        | unique        |
| frequency   | integer       |

To this table, I am doing inserts, when a duplicate occurs, I'll update the frequency column. Pseudo code looks something like this:

try {
    INSERT into WORDLIST word1
    id = lastInsertedId
} catch(Exception) {
    //if a duplicate happens
    UPDATE wordlist WHERE word = "word1"
    id = SELECT id FROM wordlist where word = "word1"
}
//save the updated/inserted id somewhere

The problem with the above code is that when a duplicate happens I am forced to do an extra select query to obtain the id of the updated row which is a performance buster and slows the app down for about 30%.

I'm open to other approaches but couldn't think of something better than this try/catch approach with an extra query

Answer 1

As word already has a unique index you can try to simplify your query by using insert or replace:

INSERT OR REPLACE into WORDLIST word1
id = last_insert_rowid()

Note that in the conflict case a new rowid/ID is created and the old one is dropped. If you need to keep the ID you could resort to using a trigger, which may perform better than handling the special case in application code.

If all you want is implement a hit counter you can look at the following answer: https://stackoverflow.com/a/42556302/5794048

Answer 2

The UPDATE statement cannot return data. If you need the ID, you need to run a SELECT.

Please note that any INSERT or UPDATE must check the word column for duplicates, so the SELECT will entirely run from cache. Any slowdown probably comes from running multiple changes with automatic transactions.

Additionally, relying on an exception to detect the duplicate will break down if there is any other error. Better check for duplicates first (which will not be any less efficient for the reason mentioned above):

BEGIN;
SELECT id FROM wordlist WHERE word = ?;
if found:
    UPDATE wordlist ... WHERE id = ?;   -- faster than word=?
else:
    INSERT INTO wordlist;
COMMIT;