How SQL actually run in Memory? Row by Row

Question

How does SQL actually run?

For example, if I want to find a row with row_id=123, will SQL query search row by row from the top of memory?

Answer 1

This is a topic of query optimization. Briefly speaking, based on your query, the database system first tries to generate and optimize a query plan that possibly has optimal performance, then executes that plan.

For selections like row_id = 123, the actually query plan depends on whether you have an index or not. If you do not, a table scan will be used to examine the table row by row. But if you do have an index on row_id, there is a chance to skip most of the rows by using the index. In this case, the DB will not search row by row.

If you're running PostgreSQL or MySQL, you can use

EXPLAIN SELECT * FROM table WHERE row_id = 123;

to see the query plan generated by your system.

For an example table,

CREATE TABLE test(row_id INT);          -- without index
COPY test FROM '/home/user/test.csv';   -- 40,000 rows

The EXPLAIN SELECT * FROM test WHERE row_id = 123 outputs:

                  QUERY PLAN                      
------------------------------------------------------
 Seq Scan on test  (cost=0.00..677.00 rows=5 width=4)
    Filter: (row_id = 123)
 (2 rows)

which means the database will do a sequential scan on the whole table and find the rows with row_id = 123.

However, if you create an index on the column row_id = 123:

CREATE INDEX test_idx ON test(row_id);

then the same EXPLAIN will tell us that the database will use an index scan to avoid going through the whole table:

                            QUERY PLAN                                
--------------------------------------------------------------------------
 Index Only Scan using test_idx on test  (cost=0.00..8.34 rows=5 width=4)
   Index Cond: (row_id = 123)
 (2 rows)

You can also use EXPLAIN ANALYZE to see actual performance of your SQL queries. On my machine, the total runtimes for sequential scan and index scan are 14.738 ms and 0.171 ms, respectively.

For details of query optimization, refer to Chapters 15 and 16 in the Database Systems: The Complete Book.