$ settings --editor

$ settings --general

$ save --settings

SQL for beginners

Speeding up queries with CREATE INDEX #

Without any help, the database has to check every single row of a table to find the ones matching a WHERE condition. On a small table like orders that is instant, but on a table with millions of rows it can be slow. An index solves this by keeping a separate, pre-sorted lookup structure for a column, so the database can jump straight to the matching rows instead of scanning the whole table.

CREATE INDEX index_name
ON table_name (column_name);
  • index_name: A name for the new index.
  • table_name (column_name): The table and column the index is built on.

An index speeds up WHERE, ORDER BY, and JOIN operations that use the indexed column, at the cost of a small amount of extra storage and slightly slower INSERT/UPDATE/DELETE operations on that column (since the index has to be kept up to date too).

Exercise #

Write a command that creates an index on the customer_id column of the orders table.

Tests #

  • An index exists on the customer_id column of the orders table.
Solution 
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);
Output will be displayed here