MySQL Interview Tricks & Tips

Essential techniques to ace your MySQL interviews and improve database performance

These tricks will help you stand out in interviews by showcasing deeper understanding of MySQL concepts.

Installation & Setup Tricks

Use Docker for Quick Setup

Docker Setup

Run MySQL without system changes using Docker:

                                        docker run --name mysql-db -e MYSQL_ROOT_PASSWORD=pass123 -p 3306:3306 -d mysql:latest
                                    

Pro Tip: Always change the default root password after installation.

Secure MySQL After Installation

Security Essential

Always change the root password and remove test databases:

                                        ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_strong_password';
                                        DROP DATABASE IF EXISTS test;
                                    

Database & Table Tricks

Clone Table Structure

Schema

Create an empty table with the same structure:

CREATE TABLE new_table LIKE original_table;

Efficient Data Insertion

Performance Optimization

Insert data from another table with conditions:

                                        INSERT INTO target_table 
                                        SELECT * FROM source_table 
                                        WHERE condition;
                                    

Query Optimization Tricks

Use EXPLAIN to Debug Queries

Optimization Debugging

Analyze query execution plan:

EXPLAIN SELECT * FROM users WHERE age > 25;

Key things to check: type (should be ref or range), rows (lower = better)

Force Index Usage

Indexing Advanced

When the optimizer doesn't choose the best index:

                                        SELECT * FROM users USE INDEX (age_index) 
                                        WHERE age > 25;
                                    

JOINs & Subqueries Tricks

Replace NOT IN with LEFT JOIN

Performance Best Practice

More efficient than NOT IN for large datasets:

                                        SELECT a.* FROM table_a a 
                                        LEFT JOIN table_b b ON a.id = b.id 
                                        WHERE b.id IS NULL;
                                    

Use EXISTS Instead of IN

Optimization Best Practice

Better performance for large subqueries:

                                        SELECT * FROM orders o 
                                        WHERE EXISTS (
                                            SELECT 1 FROM customers c 
                                            WHERE c.id = o.customer_id
                                        );
                                    

Aggregation Tricks

Top N Rows Per Group

Advanced Complex Query

Without window functions (works in all MySQL versions):

                                        SELECT t1.* FROM products t1
                                        WHERE (
                                            SELECT COUNT(*) FROM products t2 
                                            WHERE t2.category = t1.category 
                                            AND t2.price >= t1.price
                                        ) <= 3  -- Top 3 products per category
                                        ORDER BY t1.category, t1.price DESC;
                                    

Running Totals (MySQL 8.0+)

Window Functions Modern MySQL

Using window functions for cumulative sums:

                                        SELECT date, amount, 
                                               SUM(amount) OVER (ORDER BY date) AS running_total 
                                        FROM sales;
                                    

Date & Time Tricks

Last 30 Days Records

Date Functions Common Task

Find records from the last 30 days:

                                        SELECT * FROM orders 
                                        WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
                                    

Group By Time Periods

Aggregation Reporting

Group by week/month/year easily:

                                        SELECT 
                                            YEAR(order_date) AS year,
                                            MONTH(order_date) AS month,
                                            COUNT(*) AS total_orders
                                        FROM orders
                                        GROUP BY YEAR(order_date), MONTH(order_date);
                                    

Indexing Tricks

Find Unused Indexes

Performance Maintenance

Identify indexes that aren't being used (MySQL 5.7+):

SELECT * FROM sys.schema_unused_indexes;

These can be safely dropped to improve write performance.

Covering Indexes

Optimization Best Practice

Create indexes that satisfy queries without table access:

                                        CREATE INDEX idx_covering ON users(name, email);
                                        -- Query uses index only:
                                        SELECT name, email FROM users WHERE name LIKE 'A%';
                                    

Transaction Tricks

Test Before Committing

Safety Essential

Test queries in a transaction before committing:

                                        START TRANSACTION;
                                        UPDATE accounts SET balance = balance - 100 
                                        WHERE user_id = 1;
                                        -- Check for errors, then:
                                        COMMIT;  -- or ROLLBACK if issues
                                    

Set Isolation Level

Concurrency Advanced

Control how transactions interact with each other:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Other levels: READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE

Security Tricks

Audit User Privileges

Security Essential

Check what permissions a user has:

SELECT * FROM mysql.user WHERE User='username'\G

The \G formats output vertically for better readability.

Prevent SQL Injection

Best Practice Critical

Always use prepared statements in applications:

                                        // PHP Example
                                        $stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
                                        $stmt->execute(['email' => $userInput]);
                                    

Never concatenate user input directly into SQL queries!

Backup & Recovery Tricks

Single Table Backup

Maintenance Common Task

Backup just one table from command line:

mysqldump -u root -p database_name table_name > table_backup.sql

Schema-Only Backup

Structure Migration

Backup just the database structure without data:

mysqldump --no-data -u root -p database_name > schema_only.sql

Bonus: Common Interview Questions

                                        SELECT email, COUNT(*) FROM users 
                                        GROUP BY email 
                                        HAVING COUNT(*) > 1;
                                    

                                        DELETE t1 FROM users t1
                                        INNER JOIN users t2 
                                        WHERE t1.id < t2.id AND t1.email = t2.email;
                                    

DELETE: Removes rows (can rollback, logs each row)
TRUNCATE: Faster, resets auto-increment (cannot rollback)
DROP: Deletes entire table structure

Final Interview Tips

Know the difference between CHAR vs VARCHAR
Explain indexing trade-offs (speed vs storage)
Mention EXPLAIN ANALYZE (MySQL 8.0+) for deeper query analysis
Discuss AUTO_INCREMENT gaps (they happen, not a bug)