Mysql

MySQL

MySQL is a widely used relational database management system (RDBMS). It is the "M" in the LAMP stack and powers huge platforms like Facebook and WordPress.

When to Use

• Web Applications: Standard for PHP/Laravel/Wordpress ecosystems.
• Read-Heavy Workloads: Historically very fast for simple read operations.
• Replication: Huge ecosystem of tools for Master-Slave replication and clustering (Galera, InnoDB Cluster).

Quick Start

-- Upsert (Insert or Update)
INSERT INTO users (id, name) VALUES (1, 'Jane')
ON DUPLICATE KEY UPDATE name = 'Jane';

Core Concepts

Storage Engines

• InnoDB: The default. Supports transactions (ACID), row-level locking, and foreign keys. Always use this.
• MyISAM: Legacy. Fast reads but table-level locking and no transactions. Avoid.

Explain

Prefix any query with EXPLAIN to see how MySQL executes it (indexes used, rows scanned).

EXPLAIN SELECT * FROM users WHERE email = 'test@test.com';

Replication

MySQL replication is often asynchronous by default, meaning slaves might lag behind master.

Best Practices (2025)

Do:

• Use MySQL 8.4+: For LTS features like EXPLAIN ANALYZE and CTE support.
• Use utf8mb4: The real UTF-8. The standard utf8 in MySQL is a partial implementation (max 3 bytes).
• Disable mysql_native_password: In 8.4+, use caching_sha2_password for security.

Don't:

• Don't use MyISAM: It is effectively obsolete for modern apps.
• Don't store logic in Stored Procedures: Hard to version control and debug. Keep logic in the app layer.

References

• MySQL 8.4 Reference Manual