Updating using `database/sql` in Go is slow

I'm running an update query against a local MySQL instance, and it seems to be taking about 8 ms to update a single row. That seemed really slow. Is there anything I can do to improve performance?

db, _ := sql.Open("mysql", "user:pass@(localhost:3306)/dbname")
db.Exec(`CREATE TABLE topics(
			topic_id                            INT AUTO_INCREMENT PRIMARY KEY,
			title                               VARCHAR(250) NOT NULL,
			content                             TEXT,
			is_sticky                           BOOL NOT NULL DEFAULT false,
			is_readonly                         BOOL NOT NULL DEFAULT false,
			num_comments                        INT NOT NULL DEFAULT 0,
			num_views                           INT NOT NULL DEFAULT 0,
			activity_at                         DATETIME DEFAULT CURRENT_TIMESTAMP,
			archived_at                         DATETIME DEFAULT CURRENT_TIMESTAMP,
			created_at                          DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at                          DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);`)
db.Exec("INSERT INTO topics(title) VALUES(?);", "Name")

since := time.Now()
for i := 0; i < 100; i++ {
    db.Exec("UPDATE topics SET num_views = ? WHERE topic_id = 1;", i)
}
fmt.Println(time.Since(since))

It takes about 850 ms to complete 100 iterations on a Core i7-4790K. I also tried the same thing with Postgres with more or less the same result. I created this test code because my webapp was very slow at about 10 ms per request (250 requests/sec throughput) and it turned out that updates were taking a lot of time.

Also, using prepared statements made no difference.

stmt, _ := db.Prepare("UPDATE topics SET num_views = ? WHERE topic_id = ?;")
since := time.Now()
for i := 0; i < 100; i++ {
	stmt.Exec(i, 1)
}
fmt.Println(time.Since(since))

(Speaking from a MySQL point of view...)

Some "Rules of Thumb":

• Single INSERT: 10ms
• 100 or more rows inserted by a single INSERT: 10 times as fast per row.
• BEGIN; INSERT...; INSERT...; ... COMMIT; : Also 10x.
• The above assumes HDD; SSD might be another 10x faster.
• If multiple connections are each doing inserts, they may be able to run in parallel. 10 threads might be able to do 5 times the work in the same elapsed time. (Of course, this may add unwanted complexity to the app.)

Similar figures for UPDATE, though it is not easy to do different updates on different rows with a single query.

Your test shows 8.5ms per row UPDATEd when doing one row at a time. Batching either with BEGIN...COMMIT will probably take about 85ms for all 100 rows, even on HDD.

Some applications lend themselves to batching; some do not. If you want to talk about improving MySQL performance, we need to get into the details of your application.

"Like" and "View" counters may need to be moved to a 'parallel' table since they tend to be Updated one-at-a-time, with some interference with other activity. They also tend to automatically allow for multi-threading, hence much less than 850ms per 100. In really high activity (over, say, 1K views per second), such counters can be artificially batched via extra app code.

Please rewrite your benchmark to reflect the activity that will happen in the real application. (I am guessing that the Updates will happen in parallel, not serial. And they will be spread out randomly across time.)

Another thing... If each "view count" comes to a web server, then there is also connect and disconnect; hence the elapsed time is likely to be more than 8.5ms. But "elapsed" is not the critical issue; the real issue is "how many updates can be performed per second".)

And another thing... If you test 'parallel', don't hit the same row by each request. That will probably be much slower than if you hit different rows. (Hitting a random row would be better. Having a bias in which row to hit would be even more realistic.)