> For the complete documentation index, see [llms.txt](https://www.ankitavirani.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.ankitavirani.com/experience/database/mysql.md).

# MySQL

## MySQL: (RDBMS) 🐬

MySQL is a widely-used, open-source relational database management system (RDBMS) designed for efficiently storing, managing, and retrieving data. Known for its **reliability**, **scalability**, and **ease of use**, it powers many web applications and large-scale systems.

***

#### 🚀 **How MySQL Works**

MySQL organizes data into **tables** with rows and columns. Each row represents a record, while columns define the attributes of that record. This logical structure makes data retrieval fast and efficient.

***

#### 🗝️ **Key Components**

* **Relationships**: Connections between tables (e.g., one-to-one, one-to-many).
* **Tables:** The basic structure for storing data in rows and columns
* **Indexes:** Data structures that improve query performance
* **Views:** Virtual tables based on the result of a SELECT query
* **Stored Procedures:** Reusable blocks of SQL code
* **Triggers:** Functions automatically executed in response to certain events
* **Transactions:** Units of work that ensure data integrity

***

#### 💻 **SQL (Structured Query Language)**

MySQL uses SQL to interact with the database. SQL is used for:

* **Creating** and **modifying** tables.
* **Inserting**, **updating**, and **deleting** data.
* **Querying** data efficiently.

***

#### 📝 **Example SQL Code**:

```sql
sqlCopy codeCREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
);
```

This SQL command creates a table named `customers` with `id`, `name`, and `email` columns. The `id` column is the primary key and auto-increments, and the `email` column must be unique.

***

#### 🛠️ **When to Use MySQL**

MySQL is ideal for:

* **Web applications** (e.g., WordPress).
* **E-commerce platforms**.
* **Content management systems** (CMS).
* **Enterprise-grade systems**.

***

#### 📈 **Advantages of MySQL**

* **Open-source**: Free to use and distribute.
* **Reliable**: Proven performance and stability.
* **Scalable**: Manages large datasets and high traffic efficiently.
* **Community Support**: Extensive resources and help.
* **Easy to Learn**: Intuitive for beginners.

***

#### ⚖️ **Disadvantages of MySQL**

* **Performance**: May lag behind some databases for highly complex queries.
* **Schema Rigidity**: Requires predefined structures, making it less flexible compared to NoSQL databases.

***

### **MySQL Skills Overview** 🐬💡

As an experienced MySQL developer, I have comprehensive expertise in designing and managing databases using best practices. Here's a breakdown of my MySQL skills:

#### MySQL Architecture 🏗️

MySQL follows a client-server architecture. Here's a simplified diagram of its structure:

<figure><img src="https://4222757721-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FJ8enmdGTQxxu7PPuvM80%2Fuploads%2FKBQGiinskT9j8zlLtLsP%2Fimage.png?alt=media&amp;token=8730e1a4-d97d-47d1-ab0f-bb6e11462b7a" alt=""><figcaption></figcaption></figure>

#### &#x20;CRUD Operations and Advanced Queries 🔄

Proficient in **Create**, **Read**, **Update**, and **Delete** operations and executing complex queries.

```sql
-- Create a table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

-- Select data with a JOIN
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;

-- Update data
UPDATE users SET name = 'Jane Doe' WHERE id = 1;

-- Delete data
DELETE FROM users WHERE email = 'john@example.com';
```

#### &#x20;Indexing and Query Optimization 🚀

Experienced in creating and managing indexes for improved query performance:

```sql
-- Create an index
CREATE INDEX idx_user_email ON users(email);

-- Analyze query performance
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
```

#### &#x20;Advanced Features 🔬

* **Partitioning:** Dividing large tables into smaller, more manageable parts
* **Full-text Search:** Using FULLTEXT indexes for text searching
* **Stored Functions:** For complex calculations and logic
* **Events:** Scheduled tasks that run automatically

```sql
-- Table partitioning
CREATE TABLE orders (
    id INT,
    order_date DATE,
    total DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

-- Full-text search
CREATE FULLTEXT INDEX idx_article_content ON articles(content);
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('MySQL database' IN NATURAL LANGUAGE MODE);

-- Stored function
DELIMITER //
CREATE FUNCTION calculate_discount(price DECIMAL(10,2), discount_percent INT)
RETURNS DECIMAL(10,2)
BEGIN
    RETURN price - (price * discount_percent / 100);
END //
DELIMITER ;

-- Event
CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
DO
    DELETE FROM temp_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
```

#### Security and Authentication 🔒

Experienced in implementing MySQL's security features:

* User Authentication and Privileges
* SSL/TLS encryption for data in transit
* MySQL Enterprise Audit for tracking database activity

```sql
-- Create a user
CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'secret';

-- Grant privileges
GRANT SELECT ON mydatabase.* TO 'readonly'@'localhost';

-- Enable SSL for a user
ALTER USER 'username'@'localhost' REQUIRE SSL;

-- View current connections
SELECT * FROM information_schema.processlist;
```

#### Performance Monitoring and Optimization 📈

Proficient in using MySQL's built-in tools and third-party solutions for monitoring and optimizing database performance:

* SHOW PROCESSLIST for viewing current database connections
* EXPLAIN for query plan analysis
* Performance Schema for detailed performance metrics
* MySQL Enterprise Monitor for comprehensive monitoring and alerting

```sql
-- Check table status
SHOW TABLE STATUS;

-- Optimize a table
OPTIMIZE TABLE mytable;

-- View query cache statistics
SHOW STATUS LIKE 'Qcache%';

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
```

With these skills, I can design, implement, and maintain efficient and scalable MySQL databases for various applications, ensuring optimal performance, security, and reliability. 💪🚀