MySQL From the Absolute Basics

We now go one layer deeper.

Our current architecture is:

Browser
   ↓
DNS
   ↓
IP
   ↓
TCP
   ↓
TLS
   ↓
HTTP
   ↓
Nginx
   ↓
PHP-FPM
   ↓
WordPress
   ↓
MySQL

Today we study:

MySQL

This is where WordPress stores most of its structured application data.

1. What Is Data?

Before MySQL, understand the simplest concept:

Data is information represented in a form that a computer can store and process.

Examples:

Name:
Abey

Email:
example@example.com

Post title:
My First Website

Price:
499

Date:
2026-08-13

A database organizes this information.

2. What Is a Database?

A database is a system for storing and retrieving structured information.

Think:

Database
│
├── Users
├── Products
├── Orders
├── Settings
└── Content

WordPress needs a database because a website contains much more than static files.

3. Why Can’t WordPress Just Use Files?

Some information is stored in files:

WordPress
├── PHP files
├── CSS
├── JavaScript
├── Images
└── Configuration

But imagine storing thousands of posts, users, comments, settings, relationships, and metadata entirely as individual files.

Searching and managing that information would become inefficient and complicated.

A database provides structured querying.

4. MySQL

MySQL is a:

Relational Database Management System

Often abbreviated:

RDBMS

The important word is:

Relational

Data is organized into related tables.

5. MySQL Is Not WordPress

Keep these separate:

MySQL
=
database management system

WordPress
=
web application

Relationship:

WordPress
   ↓
MySQL

WordPress uses MySQL to store and retrieve its data.

6. MySQL Is Not the Database Itself

A useful distinction:

MySQL
=
software/database server

Inside MySQL you can have:

Databases

Inside databases:

Tables

Inside tables:

Rows

Inside rows:

Columns

7. Hierarchy

Memorize:

MySQL Server
    │
    ├── Database A
    │     ├── Table
    │     ├── Table
    │     └── Table
    │
    └── Database B
          ├── Table
          └── Table

For WordPress:

MySQL Server
    ↓
wordpress_database
    ↓
WordPress tables

8. Database

Suppose your WordPress database is called:

wordpress_db

It may contain:

wordpress_db
│
├── wp_posts
├── wp_users
├── wp_options
├── wp_postmeta
├── wp_terms
└── ...

The actual prefix may not be wp_.

9. Table

A table is a structured collection of related records.

Imagine:

wp_users

Conceptually:

ID	user_login	user_email
1	admin	admin@example.com
2	john	john@example.com

Each horizontal record is a:

Row

10. Column

A column represents a particular type of information.

For example:

wp_users

might contain columns such as:

ID
user_login
user_pass
user_email
user_registered

So:

Column
=
one attribute/field of the table

11. Row

A row represents one record.

Example:

1 | admin | admin@example.com

This represents one user record.

12. Cell

The intersection of:

row
+
column

is a cell/value.

For example:

user_email

for user ID 1 might contain:

admin@example.com

13. SQL

SQL means:

Structured Query Language

It is used to communicate with relational databases.

For example:

SELECT * FROM wp_users;

This means approximately:

Retrieve all rows from wp_users.

14. SQL Is a Language

Think:

PHP
=
programming language

SQL
=
database query language

WordPress PHP code can execute SQL queries against MySQL.

15. Basic SELECT

Example:

SELECT * FROM wp_posts;

Break it down:

SELECT
=
retrieve data

*
=
all selected columns

FROM
=
source table

wp_posts
=
table

16. Select Specific Columns

Instead of:

SELECT * FROM wp_users;

you can request:

SELECT ID, user_login, user_email
FROM wp_users;

This retrieves only the specified columns.

17. WHERE

Suppose you want user ID 5:

SELECT *
FROM wp_users
WHERE ID = 5;

Conceptually:

wp_users
   ↓
find ID = 5
   ↓
return matching row

18. SQL Filtering

Example:

SELECT *
FROM wp_posts
WHERE post_status = 'publish';

This asks for published posts.

19. INSERT

To add a record:

INSERT INTO ...

Conceptually:

INSERT INTO wp_users (...)
VALUES (...);

This creates a new database row.

20. UPDATE

To modify existing data:

UPDATE ...

Example conceptually:

UPDATE wp_options
SET option_value = '...'
WHERE option_name = '...';

21. DELETE

To remove records:

DELETE FROM ...

Example:

DELETE FROM table_name
WHERE id = 10;

Be extremely careful with DELETE.

22. The Most Dangerous SQL Mistake

Never casually execute:

DELETE FROM wp_posts;

without a precise understanding of what you’re doing.

Even more dangerous:

DELETE FROM wp_posts;

without a WHERE clause.

That can remove every row in the table.

23. WordPress Database

A normal WordPress installation creates a collection of tables.

Common examples:

wp_posts
wp_postmeta
wp_users
wp_usermeta
wp_options
wp_terms
wp_term_taxonomy
wp_term_relationships
wp_comments
wp_commentmeta

Plugins can create additional tables.

24. `wp_posts`

Despite the name, this table isn’t only for blog posts.

It can contain different WordPress content types, including:

Posts
Pages
Custom post types
Attachments
Revisions

The exact rows depend on the site’s content.

25. `post_type`

One important column is:

post_type

It distinguishes types of content.

Examples include:

post
page
attachment
revision

Plugins can introduce custom post types.

26. Example

Conceptually:

ID	post_title	post_type	post_status
10	About Us	page	publish
11	Welcome	post	publish
12	Logo	attachment	inherit

So one table can represent multiple types of WordPress objects.

27. `post_status`

Another important field:

post_status

Examples can include:

publish
draft
pending
private
trash
inherit

WordPress uses these states to determine how content is handled.

28. `wp_postmeta`

WordPress needs additional information about posts.

That’s where:

wp_postmeta

comes in.

Conceptually:

wp_posts
   │
   │ post ID
   ▼
wp_postmeta

29. Metadata

Metadata means:

Additional information associated with another object.

For a post:

Post
│
├── title
├── content
├── status
└── metadata
      ├── custom field
      ├── layout
      └── plugin data

30. `meta_key` and `meta_value`

wp_postmeta commonly contains:

post_id
meta_key
meta_value

Example conceptually:

post_id	meta_key	meta_value
10	page_template	default
10	custom_color	blue

The actual data depends on themes/plugins.

31. `wp_users`

This table stores user records.

Common fields include:

ID
user_login
user_pass
user_nicename
user_email
user_url
user_registered
display_name

32. Passwords

Important:

WordPress should not store user passwords as plain text.

The database contains password hashes.

Conceptually:

Password
   ↓
password hashing
   ↓
stored hash

33. Hashing vs Encryption

These are different.

Encryption

data
 ↓
encryption
 ↓
encrypted data
 ↓
decryption
 ↓
original data

Password hashing

password
 ↓
hash
 ↓
stored result

You don’t normally “decrypt” a password hash to obtain the original password.

34. `wp_usermeta`

Additional user information is commonly stored in:

wp_usermeta

For example:

user ID
role/capability metadata
preferences
plugin-specific data

35. User Relationships

Conceptually:

wp_users
   │
   │ ID
   ▼
wp_usermeta

This is an example of a relational relationship.

36. `wp_options`

One of the most important WordPress tables:

wp_options

It stores site-wide configuration/options.

Examples include:

siteurl
home
blogname
active_plugins
stylesheet
template

and many plugin/theme settings.

37. `siteurl`

WordPress commonly stores the site’s URL in:

siteurl

For example:

https://templates.cresignsys.com

38. `home`

Another important option is:

home

It represents the site’s front-end URL.

Depending on configuration, home and siteurl can be the same or intentionally different.

39. Why `wp_options` Matters

If WordPress cannot correctly read its options:

WordPress
 ↓
configuration problems
 ↓
website errors

A corrupted or incorrectly modified wp_options table can affect the entire site.

40. `wp_terms`

WordPress uses taxonomy concepts such as:

Categories
Tags
Custom taxonomies

The term information is stored through tables such as:

wp_terms
wp_term_taxonomy
wp_term_relationships

41. Taxonomy

A taxonomy is a system for grouping/classifying content.

For example:

Post
│
├── Category: Technology
├── Category: Hosting
└── Tag: Nginx

42. `wp_term_relationships`

This table connects content to taxonomy terms.

Conceptually:

Post
  │
  ▼
relationship
  │
  ▼
Term

This is a database relationship.

43. Why Multiple Tables?

You might wonder:

Why not put everything into one giant table?

Because relational databases organize information into logical structures.

Instead of:

ONE HUGE TABLE

we have:

Posts
Users
Terms
Metadata
Comments
Options

with relationships between them.

44. Database Normalization

This design principle is related to:

Normalization

Normalization attempts to organize data so that unnecessary duplication is reduced and relationships are represented cleanly.

It has several normal forms, such as:

1NF
2NF
3NF

WordPress’s schema is application-specific and isn’t a textbook example of perfectly normalized relational design everywhere, but the concepts are still important for understanding relational databases.

45. Primary Key

A table often has a:

Primary Key

It uniquely identifies a row.

For wp_posts:

ID

is the primary identifier.

Conceptually:

wp_posts
ID
│
├── 1
├── 2
├── 3
└── 4

Each identifies a particular record.

46. Why Primary Keys Matter

Suppose:

post ID = 100

WordPress can refer to that specific post.

Other tables can store:

post_id = 100

to associate information with it.

47. Foreign Key Concept

A column that refers to a record in another table is conceptually a:

Foreign Key

For example:

wp_postmeta.post_id

refers to:

wp_posts.ID

WordPress often manages these relationships at the application level rather than relying exclusively on database-enforced foreign-key constraints.

48. Index

A database index helps find data efficiently.

Think about a book.

Without an index:

Search every page

With an index:

Go directly toward the relevant location

Database indexes serve a similar purpose.

49. Example

Suppose wp_posts contains:

1,000,000 rows

Searching every row repeatedly could be expensive.

An appropriate index can dramatically improve lookup performance for supported query patterns.

50. Index Trade-Off

Indexes aren’t free.

They consume:

Disk
RAM
Write/update overhead

Therefore:

More indexes are not automatically better.

51. SQL Query Flow

When WordPress needs data:

WordPress PHP
   ↓
SQL query
   ↓
MySQL
   ↓
Query parser
   ↓
Query optimizer
   ↓
Storage engine
   ↓
Data
   ↓
Result
   ↓
WordPress

52. MySQL Storage Engine

MySQL can use storage engines.

The most common modern choice for WordPress is:

InnoDB

It provides features such as:

Transactions
Row-level locking
Crash recovery
Indexes

53. Transaction

A transaction groups database operations into a logical unit.

Conceptually:

BEGIN
   operation 1
   operation 2
   operation 3
COMMIT

If something goes wrong:

ROLLBACK

can undo the transaction’s changes, subject to the database/application behavior.

54. ACID

Database transactions are often discussed using:

ACID

A = Atomicity
C = Consistency
I = Isolation
D = Durability

55. Atomicity

A transaction should be treated as an all-or-nothing unit.

Conceptually:

3 operations
 ↓
all succeed
 ↓
COMMIT

or:

failure
 ↓
ROLLBACK

56. Consistency

The database should move from one valid state to another valid state according to its constraints and rules.

57. Isolation

Concurrent transactions should not improperly interfere with each other.

This becomes important when many WordPress requests access MySQL simultaneously.

58. Durability

Once a transaction is committed, the database system is designed to preserve the committed data through normal failures, subject to hardware, configuration, and recovery assumptions.

59. MySQL Connection

PHP-FPM workers connect to MySQL.

Conceptually:

PHP Worker
    │
    ▼
MySQL connection
    │
    ▼
MySQL server

Multiple PHP workers can create concurrent database activity.

60. Database User

WordPress normally uses a dedicated MySQL account.

For example:

wordpress_user

with permissions on:

wordpress_db

61. Least Privilege

Don’t use the MySQL root account for WordPress.

Better:

WordPress
 ↓
wordpress_user
 ↓
wordpress_db

The database account should have only the privileges required by the application.

62. WordPress `wp-config.php`

WordPress needs database connection information.

Conceptually:

define('DB_NAME', 'wordpress_db');
define('DB_USER', 'wordpress_user');
define('DB_PASSWORD', '...');
define('DB_HOST', 'localhost');

The exact values depend on your installation.

63. DB_HOST

The database server could be:

localhost

or:

127.0.0.1

or a remote database hostname.

For many single-server WordPress installations:

WordPress
 ↓
same VPS
 ↓
MySQL

64. Localhost vs 127.0.0.1

These can behave differently depending on the client library and configuration.

For MySQL on Linux, localhost often results in Unix-socket communication, while 127.0.0.1 uses TCP.

This is a useful detail when diagnosing connection problems.

65. MySQL Unix Socket

A local MySQL server may expose a socket such as:

/run/mysqld/mysqld.sock

Then:

PHP
 ↓
Unix socket
 ↓
MySQL

can occur.

66. MySQL TCP

Alternatively:

PHP
 ↓
127.0.0.1:3306
 ↓
MySQL

Port:

is the conventional MySQL TCP port.

It can be configured differently.

67. Check MySQL Service

On Ubuntu:

sudo systemctl status mysql

You may see:

Active: active (running)

68. Check MySQL Version

mysql --version

Your server has previously been using MySQL 8.x.

The exact installed version should be checked before making configuration decisions.

69. Connect to MySQL

If you have administrative access:

sudo mysql

Depending on your authentication configuration, this may open the MySQL shell.

You might see:

mysql>

70. `SHOW DATABASES`

Inside MySQL:

SHOW DATABASES;

You may see:

information_schema
mysql
performance_schema
sys
wordpress_db

71. Select a Database

USE wordpress_db;

Now subsequent table commands operate against that database unless otherwise specified.

72. Show Tables

SHOW TABLES;

You may see:

wp_options
wp_posts
wp_postmeta
wp_users
...

73. Inspect Table Structure

Use:

DESCRIBE wp_posts;

or:

SHOW COLUMNS FROM wp_posts;

This shows columns and their types.

74. Example Data Types

MySQL has data types such as:

INT
BIGINT
VARCHAR
TEXT
DATETIME
DECIMAL
BOOLEAN-like types
JSON

The appropriate type depends on the data.

75. `INT`

Used for integer values.

Example:

1
25
1000

76. `VARCHAR`

Variable-length text.

Example:

VARCHAR(255)

can store text up to a specified maximum length.

77. `TEXT`

Used for larger text content.

WordPress post content can be large, so text-oriented columns are important.

78. `DATETIME`

Stores date/time values.

WordPress has many timestamps such as:

post_date
post_modified
user_registered

79. WordPress Data Flow

When you create a new page in WordPress:

Browser
 ↓
Nginx
 ↓
PHP-FPM
 ↓
WordPress
 ↓
SQL INSERT/UPDATE
 ↓
MySQL
 ↓
database

The page’s information is stored in the database.

80. What About Images?

This is important.

WordPress image data is split between:

Database
+
Filesystem

The actual image file generally lives under:

wp-content/uploads/

while information about the attachment is stored in the database.

Conceptually:

Image
├── Actual file
│     ↓
│  filesystem
│
└── Metadata
      ↓
    MySQL

81. WordPress Is Hybrid Storage

This is a key concept:

WordPress
│
├── Code
│    ↓
│  filesystem
│
├── Media
│    ↓
│  filesystem
│
└── Structured application data
     ↓
   MySQL

82. Plugin Installation

When you install a plugin:

Plugin PHP files
 ↓
filesystem

But plugin configuration may be stored in:

wp_options

Some plugins also create their own tables.

So one plugin can use both:

Filesystem
+
Database

83. Theme Installation

Similarly:

Theme files
 ↓
wp-content/themes/

Theme settings may be stored in:

wp_options

or other WordPress metadata structures.

84. User Creation

When you create a WordPress user:

Browser
 ↓
WordPress
 ↓
MySQL

data goes into tables such as:

wp_users
wp_usermeta

85. Creating a Post

Creating a post involves information such as:

title
content
author
status
date
slug

and may involve:

wp_posts
wp_postmeta
wp_terms
wp_term_relationships

depending on the content and taxonomy.

86. Database Slowness

Suppose your website takes 5 seconds to generate a page.

Possible chain:

Nginx
 ↓
PHP-FPM
 ↓
WordPress
 ↓
MySQL
 ↓
slow query

So the browser may experience:

5-second response

even though Nginx itself is fast.

87. Database Query Optimization

One area of performance tuning is:

SQL query
 ↓
EXPLAIN
 ↓
query plan
 ↓
identify bottleneck

For example:

EXPLAIN SELECT ...

This helps understand how MySQL intends to execute a query.

88. Slow Queries

MySQL can record slow queries through its slow-query log.

Conceptually:

WordPress
 ↓
slow SQL
 ↓
MySQL
 ↓
slow query log

This is useful when diagnosing database performance.

89. Database Size

Check the database size from MySQL.

For example:

SELECT
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'wordpress_db'
GROUP BY table_schema;

This can help identify large databases.

90. Large WordPress Databases

A WordPress database can grow because of:

Posts
Revisions
Comments
Metadata
Plugin data
WooCommerce data
Logs
Transients
Analytics

Plugins are often responsible for significant database growth.

91. Database Backups

A WordPress backup is not complete if you only copy:

public/

You also need the database.

Conceptually:

Complete WordPress backup
│
├── Files
│
└── Database

92. Files Backup

For example:

wp-content/
wp-config.php
WordPress files

plus any relevant server configuration.

93. Database Backup

A common tool is:

mysqldump

For example, conceptually:

mysqldump -u wordpress_user -p wordpress_db > wordpress.sql

This creates a logical SQL backup.

Be careful with credentials and backup file permissions.

94. Restore

A SQL dump can be restored into a database.

Conceptually:

wordpress.sql
 ↓
MySQL
 ↓
database

A proper restore process should be tested rather than assumed.

95. Disaster Recovery

For your hosting platform:

Website
│
├── Files backup
├── Database backup
├── Nginx configuration
├── SSL configuration/state
└── DNS information

A real backup strategy should also include off-server storage and restoration testing.

96. MySQL Is a Separate Service

Your VPS may have:

nginx.service
php8.x-fpm.service
mysql.service

These are separate processes/services.

Conceptually:

Ubuntu
│
├── Nginx
├── PHP-FPM
└── MySQL

97. If MySQL Stops

The website may still load static files:

/logo.png
/style.css

but dynamic WordPress requests may fail or behave incorrectly because WordPress cannot retrieve its database data.

98. If PHP-FPM Stops

Nginx may still serve static files.

But PHP requests can fail:

.php
 ↓
PHP-FPM unavailable
 ↓
502

99. If Nginx Stops

The web server itself becomes unavailable even if:

PHP-FPM ✓
MySQL ✓

This illustrates why services form a chain.

100. Service Dependency Chain

Browser
   ↓
Nginx
   ↓
PHP-FPM
   ↓
WordPress
   ↓
MySQL

A failure at one layer can affect everything above it.

101. The Deep Architecture

We can now draw the complete architecture we’ve learned:

                         INTERNET
                            │
                            ▼
                           DNS
                            │
                            ▼
                         IP/Route
                            │
                            ▼
                       TCP / QUIC
                            │
                            ▼
                           TLS
                            │
                            ▼
                          HTTP
                            │
                            ▼
                          NGINX
                            │
                         FastCGI
                            │
                            ▼
                         PHP-FPM
                            │
                            ▼
                       PHP Worker
                            │
                            ▼
                         WORDPRESS
                         /       \
                        /         \
                       ▼           ▼
                 FILESYSTEM      MySQL
                    │              │
                    ▼              ▼
               wp-content/     wp_posts
               plugins/        wp_users
               themes/         wp_options
               uploads/       wp_postmeta
                               wp_terms

This is now a real hosting architecture rather than just a list of technologies.

102. The Three Major Storage Areas

For WordPress, think in three categories:

1. Application code

PHP
CSS
JS

stored primarily in the filesystem.

2. Media

Images
Videos
Documents

stored primarily in the filesystem.

3. Structured application data

Posts
Users
Settings
Metadata
Relationships

stored primarily in MySQL.

103. Most Important MySQL Vocabulary

Memorize:

Database
=
collection of related tables

Table
=
structured collection of records

Row
=
one record

Column
=
one field/attribute

SQL
=
database query language

Primary Key
=
unique row identifier

Index
=
data structure for efficient lookup

Query
=
request to database

Transaction
=
group of database operations

MySQL
=
relational database management system

104. WordPress Vocabulary

Memorize:

wp_posts
=
posts/pages/content

wp_postmeta
=
post metadata

wp_users
=
users

wp_usermeta
=
user metadata

wp_options
=
site/application settings

wp_terms
=
taxonomy terms

wp_term_relationships
=
content ↔ taxonomy relationships

The prefix may differ from wp_.

105. The Complete Request Now

A visitor requests:

https://templates.cresignsys.com/about/

The full journey is:

Browser
 ↓
DNS
 ↓
IP
 ↓
TCP
 ↓
TLS
 ↓
HTTP
 ↓
Nginx
 ↓
FastCGI
 ↓
PHP-FPM
 ↓
PHP
 ↓
WordPress
 ↓
SQL
 ↓
MySQL
 ↓
wp_posts / wp_options / etc.
 ↓
result
 ↓
WordPress
 ↓
PHP
 ↓
PHP-FPM
 ↓
Nginx
 ↓
TLS
 ↓
Browser

106. The Next Layer

We now understand:

Internet
DNS
IP
TCP
TLS
HTTP
Nginx
Linux filesystem
Linux permissions
PHP-FPM
PHP
WordPress
MySQL

The next question is:

How does MySQL itself store data on the disk?

That takes us deeper into:

MySQL
 ↓
InnoDB
 ↓
pages
 ↓
indexes
 ↓
buffer pool
 ↓
redo log
 ↓
undo log
 ↓
disk

That is where database theory meets actual server storage.

Next Lesson — 046

MySQL Deep Internals — From SQL Query to Disk

We will trace:

WordPress
 ↓
SQL
 ↓
MySQL
 ↓
Query Parser
 ↓
Optimizer
 ↓
Execution
 ↓
InnoDB
 ↓
Buffer Pool
 ↓
Indexes
 ↓
Data Pages
 ↓
Redo Log
 ↓
Disk

Then we will connect this to why a WordPress website becomes slow, how database indexes work, why RAM matters, what MySQL cache/buffer memory does, and how to diagnose MySQL performance on your Ubuntu VPS.

CresignSys Learn — Lesson 045

MySQL From the Absolute Basics

MySQL

1. What Is Data?

2. What Is a Database?

3. Why Can’t WordPress Just Use Files?

4. MySQL

Relational Database Management System

RDBMS

Relational

5. MySQL Is Not WordPress

6. MySQL Is Not the Database Itself

7. Hierarchy

8. Database

9. Table

Row

10. Column

11. Row

12. Cell

13. SQL

Structured Query Language

14. SQL Is a Language

15. Basic SELECT

16. Select Specific Columns

17. WHERE

18. SQL Filtering

19. INSERT

20. UPDATE

21. DELETE

22. The Most Dangerous SQL Mistake

23. WordPress Database

24. wp_posts

25. post_type

26. Example

27. post_status

28. wp_postmeta

29. Metadata

30. meta_key and meta_value

31. wp_users

32. Passwords

33. Hashing vs Encryption

Encryption

Password hashing

34. wp_usermeta

35. User Relationships

36. wp_options

37. siteurl

38. home

39. Why wp_options Matters

40. wp_terms

41. Taxonomy

42. wp_term_relationships

43. Why Multiple Tables?

44. Database Normalization

Normalization

45. Primary Key

Primary Key

46. Why Primary Keys Matter

47. Foreign Key Concept

Foreign Key

48. Index

49. Example

50. Index Trade-Off

51. SQL Query Flow

52. MySQL Storage Engine

InnoDB

53. Transaction

54. ACID

ACID

55. Atomicity

56. Consistency

57. Isolation

58. Durability

59. MySQL Connection

60. Database User

61. Least Privilege

62. WordPress wp-config.php

63. DB_HOST

64. Localhost vs 127.0.0.1

65. MySQL Unix Socket

24. `wp_posts`

25. `post_type`

27. `post_status`

28. `wp_postmeta`

30. `meta_key` and `meta_value`

31. `wp_users`

34. `wp_usermeta`

36. `wp_options`

37. `siteurl`

38. `home`

39. Why `wp_options` Matters

40. `wp_terms`

42. `wp_term_relationships`

62. WordPress `wp-config.php`

70. `SHOW DATABASES`

75. `INT`

76. `VARCHAR`

77. `TEXT`

78. `DATETIME`