What are the rules for key query (index) definition?

The rules for key query (index) definition revolve around optimizing database performance by creating efficient access paths to data. Here are the key principles and an example:

Rules:

1. Selectivity: Choose columns with high selectivity (unique or near-unique values) for indexes, as they filter data more effectively.
   Example: A user_id in a users table is a good candidate because it’s usually unique.

2. Query Patterns: Index columns frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses.
   Example: If queries often filter by order_date, index this column in an orders table.

3. Composite Indexes: For queries filtering on multiple columns, create a composite index in the order of most selective to least selective.
   Example: For queries like WHERE country = 'US' AND city = 'New York', create an index on (country, city).

4. Avoid Over-Indexing: Too many indexes slow down INSERT, UPDATE, and DELETE operations. Balance read and write performance.

5. Data Types: Use appropriate data types for indexed columns to minimize storage and improve lookup speed.

6. Covering Indexes: Include all columns needed for a query in the index to avoid accessing the table directly (reduces I/O).
   Example: If a query selects name and email from a users table filtered by user_id, create an index on (user_id, name, email).

Example:

Suppose you have a products table with columns product_id, category, and price.

• If queries frequently filter by category and sort by price, create a composite index:

  CREATE INDEX idx_category_price ON products (category, price);
  

• If product_id is the primary key, it’s already indexed for unique lookups.

For cloud-based database solutions, Tencent Cloud Database (TDSQL) offers automated indexing recommendations and performance optimization tools to help define efficient indexes based on query patterns.