- DCL — Data Control Language
Command for Transactions.
- DDL — Data Definition Language
command that change tables.
- DML — Data Manipulation Language
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
- Maybe you dont need timestamps for some tables
A look at writing a
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: