MySQL

MySQL

What I Do

I provide guidance on MySQL, one of the most popular open-source relational databases. I help with schema design, query optimization, replication setup, InnoDB configuration, and best practices for web applications.

When to Use Me

• Building LAMP/LEMP stack applications
• Web applications requiring reliable data storage
• Need for simple, well-documented database solution
• Implementing read replicas for scaling
• JSON document storage (MySQL 5.7+)
• Full-text search capabilities

Core Concepts

• InnoDB: ACID-compliant storage engine with row-level locking
• MyISAM: Legacy engine with full-text indexing (read-heavy scenarios)
• ACID Properties: Transaction support with InnoDB
• Replication: Master-slave, master-master, GTID-based
• Index Types: B-tree, Hash, Full-text, Spatial
• JSON Support: JSON data type with functions (MySQL 5.7+)
• Partitioning: RANGE, LIST, HASH, KEY partitions
• Stored Procedures: Server-side logic execution
• Triggers: Automated actions on DML events
• Query Cache: Deprecated in 8.0, use application caching instead

Code Examples

Basic Connection and Query

import mysql.connector
from mysql.connector import Error

def get_user_by_email(email: str) -> dict:
    conn = mysql.connector.connect(
        host="localhost",
        database="app_db",
        user="admin",
        password="secret"
    )
    try:
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT id, email, name FROM users WHERE email = %s", (email,))
        return cursor.fetchone()
    finally:
        cursor.close()
        conn.close()

Bulk Insert with Transaction

import mysql.connector

def bulk_insert_users(users: list) -> int:
    conn = mysql.connector.connect(host="localhost", database="app_db", user="admin")
    try:
        cursor = conn.cursor()
        sql = "INSERT INTO users (email, name, created_at) VALUES (%s, %s, NOW())"
        cursor.executemany(sql, [(u['email'], u['name']) for u in users])
        conn.commit()
        return cursor.rowcount
    except Error as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

Stored Procedure Call

import mysql.connector

def get_user_with_orders(user_id: int) -> tuple:
    conn = mysql.connector.connect(host="localhost", database="app_db", user="admin")
    try:
        cursor = conn.cursor(dictionary=True)
        cursor.callproc('get_user_and_orders', [user_id])
        results = []
        for result in cursor.stored_results():
            results.extend(result.fetchall())
        return results
    finally:
        cursor.close()
        conn.close()

JSON Column Query

import mysql.connector

def find_products_by_category(category: str) -> list:
    conn = mysql.connector.connect(host="localhost", database="app_db", user="admin")
    try:
        cursor = conn.cursor(dictionary=True)
        cursor.execute(
            """
            SELECT id, name, attributes
            FROM products
            WHERE JSON_CONTAINS(attributes, %s)
            """,
            (json.dumps({'category': category}),)
        )
        return cursor.fetchall()
    finally:
        cursor.close()
        conn.close()

Best Practices

1. Use InnoDB as the default storage engine
2. Create indexes based on WHERE and JOIN clauses
3. Use EXPLAIN to analyze query performance
4. Avoid SELECT *; specify needed columns explicitly
5. Use connection pooling for high concurrency
6. Set appropriate innodb_buffer_pool_size (70-80% of RAM)
7. Use prepared statements for repeated queries
8. Implement proper backup and recovery procedures
9. Enable slow query log for optimization opportunities
10. Use read replicas for read-heavy workloads

Common Patterns

Auto-Increment ID:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Soft Delete:

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;
CREATE INDEX idx_users_active ON users (deleted_at) WHERE deleted_at IS NULL;

Upsert (MySQL 8.0+):

INSERT INTO page_views (page_id, views)
VALUES (123, 1)
ON DUPLICATE KEY UPDATE views = views + 1;