---
description: This rule explains MySQL database design patterns and query optimization techniques.
globs: **/*..sql
alwaysApply: false
---
# MySQL rules
- Use appropriate data types to optimize storage and performance (e.g., INT for IDs, VARCHAR with appropriate length)
- Create indexes for columns used in WHERE, JOIN, and ORDER BY clauses
- Use foreign keys to maintain referential integrity
- Use EXPLAIN to analyze and optimize queries
- Avoid using SELECT * and only retrieve needed columns
- Use prepared statements to prevent SQL injection
- Use appropriate character set and collation (e.g., utf8mb4_unicode_ci)
- Use transactions for operations that must be atomic
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.
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:
The rule encourages using appropriate data types to optimize storage and performance. For example:
INT
for ID columnsVARCHAR
with appropriate length limitations rather than oversized fieldsDATETIME
for timestamps)Using proper data types reduces storage requirements and improves query performance.
Indexes are critical for MySQL performance. The rule recommends creating indexes for columns used in:
WHERE
clauses (filtering)JOIN
conditionsORDER BY
operationsWithout proper indexes, MySQL must scan entire tables, resulting in slow query performance as data volumes grow.
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.
Several query optimization techniques are highlighted:
EXPLAIN
to analyze query execution plansSELECT *
in favor of selecting only needed columnsThe rule recommends using prepared statements to prevent SQL injection attacks, one of the most common security vulnerabilities in database applications.
Setting appropriate character sets and collations (such as utf8mb4_unicode_ci
) ensures proper handling of international characters and special symbols.
For operations that need to be atomic (either all succeed or all fail), the rule suggests using transactions to maintain data integrity.
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.
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;
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);
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 = '[email protected]';
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.