alt Hacker NewsIn the 2nd section you're using a CREATE TABLE plus three separate ALTER TABLE calls to add the virtual columns. In the 3rd section you're using a single CREATE TABLE with the virtual columns included from the get go.
Why?
Replies
Depending on the amount of inserts, it might be more efficient to create all the indexes in one go. I think this is certainly true for normal columns.
But I suspect with JSON the overhead of parsing it each time might make it more efficient to update all the indices with every insert.
Then again, it's probably quicker still to insert the raw SQL into a temporary table in memory and then insert all of the new rows into the indexed table as a single query.
In 2 they show how to add virtual columns to an existing table, in 3 how to add indexes to existing virtual columns so they are pre-cooked. Like a cooking show.
I think the intent is to separate the virtual column creation out when it’s introduced in order to highlight that it’s a very lightweight operation. When moving onto the 3rd example, the existence of the virtual columns is just a given.