UPDATE vs COUNT vs SELECT performance

Question

Is this statement true or false

The performance of these queries

SELECT * FROM table;

UPDATE table SET field = 1;

SELECT COUNT(*) FROM table;

Are identical

Or is there ever a case in which the performance of one will greatly differ from the other?

UPDATE

1. I'm more interested if there's a large difference between the SELECT and the UPDATE. You can ignore the COUNT(*) if you want
2. Assume the select performs full table scan. The update will also perform update on all rows in the table.
3. Assume the update is only updating one field - though it will update all rows (it's an indexed field)
4. I know that they'll take different time and that they do different things. What I want to know is if the difference will be significant or not. EG. If the update will take 5 times longer than the select then it's significant. Use this as the threshold. And there's no need to be precise. Just give an approximation.

Answer 1

There are different resource types involved:

• disk I/O (this is the most costly part of every DBMS)
• buffer pressure: fetching a row will cause fetching a page from disk, which will need buffer memory to be stored in
• work/scratch memory for intermediate tables, structures and aggregates.
• "terminal" I/O to the front-end process.
• cost of locking, serialisation and versioning and journaling
• CPU cost : this is neglectable in most cases (compared to disk I/O)

The UPDATE query in the question is the hardest: it will cause all disk pages for the table to be fetched, put into buffers, altered into new buffers and written back to disk. In normal circumstances, it will also cause other processes to be locked out, with contention and even more buffer pressure as a result.

The SELECT * query needs all the pages, too; and it needs to convert/format them all into frontend-format and send them back to the frontend.

The SELECT COUNT(*) is the cheapest, on all resources. In the worst case all the disk pages have to be fetched. If an index is present, fewer disk- I/O and buffers are needed. The CPU cost is still neglectable (IMHO) and the "terminal" output is marginal.

Answer 2

When you say "performance", do you mean "how long it takes them to execute"?

• One of them is returning all data in all rows.
• One of them (if you remove the "FROM") is writing data to the rows.
• One is counting rows and returning none of the data in the rows.

All three of those queries are doing entirely different things. Therefore, it is reasonable to conclude that all three of them will take different amounts of time to complete.

Most importantly, why are you asking this question? What are you trying to solve? I have a bad feeling that you're going down a wrong path by asking this.