Sql Programming Tutorials, Guides & Best Practices

This MySQL cheatsheet provides a comprehensive overview of the most commonly used MySQL commands, complete with examples to help you quickly find the information you need. Whether you're creating and managing databases, writing queries, or handling transactions, this guide serves as a quick reference to help you work more efficiently with MySQL.

LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE your_table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

• /path/to/data.csv: The path to the CSV file.
• your_table_name: The table into which the data will be imported.
• FIELDS TERMINATED BY ',': Specifies the field delimiter.
• ENCLOSED BY '"': Specifies the field enclosure character (if any).
• LINES TERMINATED BY '\n': Specifies the line terminator.
• IGNORE 1 LINES: Ignores the header line in the CSV file (if present).

While indexes improve read performance, they can slow down write operations (INSERT, UPDATE, DELETE). Only create indexes on columns that benefit the most from indexing.

The EXPLAIN command provides insights into how MySQL executes a query. It helps identify performance bottlenecks and areas for improvement.

MySQL provides different isolation levels to control how transactions interact with each other. Each level offers a different balance between data consistency and performance:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT table_name AS "Table",
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC
LIMIT 1;

• table_name: The name of each table in the specified database.
• ORDER BY (data_length + index_length) DESC: Orders the tables by size in descending order, so the largest appears first.
• LIMIT 1: Limits the result to only the largest table.