Engineering/Database

How to Read SQL Execution Plans with EXPLAIN

When a SQL query is slow, the first instinct is often to add an index.That may solve the problem, but it may also miss the real cause. The query might already have a usable index that the optimizer decided not to use. The join order may be inefficient, the optimizer may have estimated the wrong number of rows, or the database may be sorting a large intermediate result.Consider the following quer..

Engineering/Database

Database Indexes and SQL Execution Plans

Suppose we have the following orders table:CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, customer_id BIGINT NOT NULL, order_status VARCHAR(20) NOT NULL, ordered_at DATETIME NOT NULL, total_amount BIGINT NOT NULL);When the table contains only a few hundred rows, the following query returns almost immediately:SELECT *FROM ordersWHERE customer_id = 1001;The situation changes whe..