Cursor Rules for
MySQL

The MySQL rule in Cursor provides guidance on database design patterns and query optimization techniques for MySQL databases. It helps you follow best practices for efficient database schema design, query performance, and security considerations when working with MySQL.

What this rule does

This rule provides a set of best practices and recommendations for working with MySQL databases. It covers fundamental aspects of database design, performance optimization, and security considerations that are essential for building robust MySQL-based applications.

The rule specifically focuses on:

• Appropriate data type selection
• Index optimization
• Referential integrity
• Query performance
• Security best practices
• Character encoding
• Transaction management

Key MySQL best practices

Data type optimization

The rule encourages using appropriate data types to optimize storage and performance. For example:

• Use INT for ID columns
• Use VARCHAR with appropriate length limitations rather than oversized fields
• Choose specialized types when applicable (such as DATETIME for timestamps)

Using proper data types reduces storage requirements and improves query performance.

Index creation

Indexes are critical for MySQL performance. The rule recommends creating indexes for columns used in:

• WHERE clauses (filtering)
• JOIN conditions
• ORDER BY operations

Without proper indexes, MySQL must scan entire tables, resulting in slow query performance as data volumes grow.

Referential integrity

The rule emphasizes using foreign keys to maintain referential integrity between related tables. This ensures data consistency by preventing orphaned records and enforcing relationships at the database level.

Query optimization

Several query optimization techniques are highlighted:

• Using EXPLAIN to analyze query execution plans
• Avoiding SELECT * in favor of selecting only needed columns
• Writing efficient queries that leverage indexes

Security considerations

The rule recommends using prepared statements to prevent SQL injection attacks, one of the most common security vulnerabilities in database applications.

Character encoding

Setting appropriate character sets and collations (such as utf8mb4_unicode_ci) ensures proper handling of international characters and special symbols.

Transaction management

For operations that need to be atomic (either all succeed or all fail), the rule suggests using transactions to maintain data integrity.

Using MySQL in Cursor

The MySQL rule (mysql.mdc) is designed to provide context to the AI when you're working with MySQL database files. Based on the glob pattern **/*..sql, this rule is likely set up as an "Auto Attached" rule, meaning it will be automatically applied when you open or work with SQL files in your project.

When this rule is active, Cursor's AI assistant will understand MySQL best practices and can provide better suggestions, code completions, and answers to your questions specifically tailored to MySQL database development.

You can also manually invoke this rule by typing @mysql in the chat or Cmd-K prompt if you need MySQL-specific assistance while working on files that don't match the glob pattern.

Practical usage tips

Analyzing existing queries

When optimizing an existing query, use the EXPLAIN statement to identify performance bottlenecks:

EXPLAIN SELECT users.name, orders.order_date 
FROM users 
JOIN orders ON users.id = orders.user_id 
WHERE orders.status = 'completed'
ORDER BY orders.order_date DESC;

Creating optimized indexes

Create targeted indexes for your specific query patterns:

-- Index for filtering by status and sorting by date
CREATE INDEX idx_orders_status_date ON orders (status, order_date);

-- Index for the join condition
CREATE INDEX idx_orders_user_id ON orders (user_id);

Using prepared statements

Implement prepared statements to prevent SQL injection:

-- Using prepared statements in a server-side language
PREPARE stmt FROM 'SELECT * FROM users WHERE email = ?';
SET @email = 'user@example.com';
EXECUTE stmt USING @email;
DEALLOCATE PREPARE stmt;

By following these MySQL best practices as guided by the rule, you'll develop more efficient, secure, and maintainable database implementations.