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
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.