Llaves Primarias e Índices

Database optimization is a fundamental aspect for any developer or system administrator. Properly understanding the use of keys and indexes can make a significant difference in the performance of our applications, saving processing time and, consequently, economic resources. Although these concepts often seem complex or unnecessary, their proper implementation can completely transform the efficiency of our queries.

What are keys and what is their importance in MySQL?

Keys in MySQL are elements that help guarantee the integrity of the information in our database. There are three main types:

1. Primary Key: We have seen it in each of our tables, generally associated with an ID field.
2. Unique Key: Ensures that in a specific column, a value can only appear once.
3. Foreign Key: Establishes a "hard" relationship between tables, allowing or not to add values depending on whether they are related to another table.

These keys are fundamental to maintain the correct and complete normalization of our database. For example, in a client table, we could have:

CREATE TABLE clients ( client_id INT PRIMARY KEY, email VARCHAR(100) UNIQUE NOT NULL, name VARCHAR(100), phone_number VARCHAR(15));

In this case, client_id is our primary key, while email has a uniqueness constraint, ensuring that there are no duplicates.

What are indexes and how do they improve performance?

An index, simply explained, is a special table that MySQL creates invisibly to the user. This table stores certain data from our tables in a sorted and optimized way, which makes searches incredibly fast.

Indexes take up disk space, which is important to consider, but the space used is more than offset by the savings in query time. As a rule of thumb, we should index the columns we use the most in our critical queries.

Let's look at a practical example:

-- Query without indexSELECT * FROM clients WHERE name = 'Twila Rosenbaum';-- Time: 0.08 seconds
-- We create an index on the name columnCREATE INDEX idx_clients_name ON clients (name);
-- The same query with indexSELECT * FROM clients WHERE name = 'Twila Rosenbaum';-- Time: 0.00 seconds (less than one hundredth).

As we can see, the difference is remarkable even in a table with relatively few records. In databases with millions of records, this optimization can be crucial.

How do indexes with multiple columns work?

A fundamental aspect of indexes involving multiple columns is the order in which they are defined. This order is crucial and directly affects query efficiency.

For example, these two indexes are completely different:

CREATE INDEX idx_a ON clients (name, email);CREATE INDEX idx_b ON clients (email, name);

The first index will optimize queries where you filter first by name and then by email, while the second will do the opposite. The order should match the order of the conditions in our most frequent queries.

-- Optimized by idx_aSELECT * FROM clients WHERE name = 'John' AND email = '[email protected]';
-- Optimized by idx_bSELECT * FROM clients WHERE email = '[email protected]' AND name = 'John';

When and where to apply indexes in our tables?

To determine where to apply indexes, we must identify:

1. The queries most used by our application
2. The WHERE and ORDER BY clauses of these queries
3. The columns that appear in these clauses

It is important to understand that not all columns need indexes. We should focus on those that:

• Appear frequently in filtering conditions
• Are used to sort results
• Have a good cardinality (many distinct values)

We can also create indexes on different types of data:

-- Index on a date column in descending orderCREATE INDEX idx_clients_created ON clients (created_at DESC);

This type of index is especially useful for queries that sort results by date, such as "show most recent users first".

Indexes can be applied to columns that are not unique, such as phone numbers, prices or dates. The important thing is that they are frequently used columns in our queries.

Index optimization may seem small in terms of microseconds, but when these queries are executed thousands of times a day, the cumulative savings in CPU time and resources can be significant, translating into real economic savings in cloud environments.

The correct implementation of keys and indexes is an art that requires understanding both the structure of our data and the patterns of access to it. With this understanding, we can design databases that not only ensure data integrity, but also provide optimal performance for our applications.

Have you noticed significant improvements by implementing indexes in your databases? Share your experience and the strategies you have used to optimize your queries.