Whether mysql creates an index on a foreign key

Typically, indexes should be created on the foreign key fields to speed up foreign key constraint checks and associated queries. However, if the table is small or the foreign key field is already a primary/unique key, you can consider not creating an index.

MySQL foreign key index: Is it a blessing or a disaster?

Many developers will be confused about a question when establishing MySQL foreign key relationships: Should we create indexes on foreign key fields? The answer is not that simple, not a simple "yes" or "no". In this article, we will talk about this issue. Not only will we tell you how to do it, but more importantly, we will tell you why you do it.

Let’s talk about the conclusion first: Normally, you need to create an index on the foreign key field. But the word "usually" has hidden mystery.

Why index? Is this still necessary? Database index is like a library directory. You can’t flip through one book at a time, right? Foreign key indexing accelerates foreign key constraint checking, as well as foreign key-based association queries. Think about it, there is no index. Every time you check whether the foreign key exists, you have to scan the entire table. That efficiency... is simply unimaginable. Especially when the data volume is large, it is simply a disaster film scene.

However, indexing is not omnipotent. It takes up extra storage space and adds some overhead when data is inserted, updated and deleted. Therefore, blindly indexing all foreign key fields may be counterproductive and will not be worth the loss.

So, when can I consider unindexed?

One case is that your table is small and your data volume is very limited. At this time, the overhead of full table scanning can be negligible, and the additional overhead caused by adding indexes is more significant.

Another case is that your foreign key field is itself a primary or unique key. In this case, the database system will automatically create an index, and you will create an index manually is a repetitive work, which is a waste of resources.

Let's use the code to experience it:

Suppose there are two tables: users and orders . user_id in the orders table is a foreign key, and the primary key id of the associated users table.

 <code class="sql">-- 创建users 表CREATE TABLE users ( id INT PRIMARY KEY, username VARCHAR(255) ); -- 创建orders 表，并添加外键约束和索引CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, amount DECIMAL(10, 2), FOREIGN KEY (user_id) REFERENCES users(id) ); -- 创建索引(通常情况下需要) CREATE INDEX idx_user_id ON orders (user_id); -- 或者，直接在建表时添加索引CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, amount DECIMAL(10, 2), FOREIGN KEY (user_id) REFERENCES users(id), INDEX idx_user_id (user_id) ); -- 测试查询SELECT * FROM orders WHERE user_id = 1;</code>

This code shows two ways to create foreign key indexes, one is to create an index separately, and the other is to create an index while creating a table. Which method to choose depends on your personal preferences and project specifications.

Remember, database optimization is a complex topic and there are no one-size-fits-all best practices. You need to weigh the pros and cons based on the actual situation and make the best choice. Don’t forget to monitor your database performance and analyze query statements regularly to truly master the essence of database optimization. Don’t be superstitious about the so-called “best practice”. Practice to produce true knowledge is the king.

Finally, let me emphasize one more thing: don’t over-optimize! Premature optimization is the source of all evil. The most efficient way is to let your application run first and then optimize according to actual conditions. Don’t optimize for the sake of optimization, it won’t be worth the loss!

The above is the detailed content of Whether mysql creates an index on a foreign key. For more information, please follow other related articles on the PHP Chinese website!