Database

  • DCL — Data Control Language
    Command for Transactions.
  • DDL — Data Definition Language
    command that change tables.
  • DML — Data Manipulation Language
    SELECT, INSERT.

Database has functions for filter out the data (WHERE) and functions for aggregate the data (aggregate functions).

Disk <-> Database <-> Web Server <-> Response <-> Client
  • Pick PostgreSQL to start your business.
  • Add a Primary key
  • Dont add index for a small table. Because even you got an index, the table is too small, database will do a Full Table Scan because it is faster. So index is pointless.
  • Use NOT NULL, foreign key constraints aggressively
  • foreign key should be indexed
  • Index is not free. Index makes insert, update, delete slower. Index takes storage.
  • Consider Composite index Partial Index over Single Column Index
  • Column appear on join condition => index candidate
  • 15% rows => Index OK.
  • Low cardinality = very few possible rows
  • Sharding
  • Maybe you dont need timestamps for some tables

A look at writing a SELECT query:

SELECT      expressions
FROM        table
WHERE       conditions
GROUP BY    columns
HAVING      conditions
ORDER BY    columns
LIMIT       value

expressions: function or column name or constant

The order of database parsing your query:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
  7. LIMIT