What features should be considered when designing indexes in a database?

When designing indexes in a database, the following points are important:

• Choosing columns for indexing: index those columns that are frequently used in WHERE conditions, JOINs, or ORDER BY.
• Type of index: there are different types of indexes (B-Tree, Hash, full-text, etc.), and the choice depends on the nature of the queries.
• Size and number of indexes: too many indexes slow down insert, update, and delete operations because indexes need to be updated.
• Data cardinality: indexes are more effective on columns with a large number of unique values.
• Composite indexes: useful if filtering is often done on multiple columns simultaneously.
• Order of columns in a composite index: important for search efficiency.

Example of creating an index in SQL:

CREATE INDEX idx_user_email ON users(email);