increment counter column in one table if ip address exists in another table MYSQL

Question

I have a viewCount column in table_1. Every time a user with a new ip address visits the page I increment viewCount column in by 1. This is to keep record of how many people have visited the page. In table table_2, I keep the ip address of the users as well as the id of the row in table_1. Here is how columns might look like and a possible scenario case when I make an update query, which I am currently trying to optimize.

tabel_1:

------------------
| id | viewCount |
------------------
| 1  |   35      |
------------------

table_2:

----------------------
| tb1_id | ipAddress |
----------------------
|   1    | 0.1.0.1   |
----------------------

Possible scenario: a user with an ip address of 0.1.0.0 visits the page. I query table_2 to see if a row with tb1_id and current user's ip address exists. if not i save the non-existing user's ip address to table_2 and increment viewCount in table_1.

My question is how can I make only 1 query (i.e one trip to database) and perform this action?

This is the code that I came up with but, although working just fine, it takes more than 1 trip

        const [rows] = await db.query(
            `
            SELECT 
                ipAddress
            FROM
                PageViews
            WHERE
                ipAddress = ?
            `,
            ['ipAddress']
        );
        if (rows.length === 0) {
            await db.query(
                `
                INSERT INTO
                    PageViews
                SET
                    projectId = ?
                AND
                    ipAddress = ?
                `,
                [projectId, ipAddress]
            );
            await db.query(
                `
                UPDATE
                    Project
                SET 
                    viewCount = viewCount + 1
                WHERE
                    id = ?
                `,
                [projectId]
            );
        }

Any help towards making the code more efficient and trips to DB fewer will be much appreciated

Answer 1

Unless I missed a requirement, I think you are working too hard. Consider this:

CREATE TABLE vc (
    ip VARCHAR(15) CHARSET ascii,
    ctr INT UNSIGNED,
    PRIMARY KEY(IP)
) ENGINE=InnoDB;

and

INSERT INTO vc (ip, ctr)
    VALUES (?, 1)               -- Does INSERT when new IP
    ON DUPLICATE KEY UPDATE ctr = ctr + 1;  -- or Increments when existing

An AUTO_INCREMENT would only be clutter and slow things down.