CODEWITHSUNDEEP
mysqlsqlindexingforeign-keysdatabase-indexes
Israel Fonseca
Israel Fonseca

Reputation: 1083

MySQL: Create a Foreign key without an Index

Is it possible to have a foreign key without an index in MySQL 5.6.34? I want that because I created a nullable column in 20M rows with a foreign key to another table. As this is a new feature, only the new rows MAY have this column filled with an actual value, and as you may expect, the cardinality of that index becomes horrible. So, for most of the time, using that index is actually a bad idea. The problem: I have tons of queries that shares this same restriction:

[...] from large_table where tenant_id = ? and nullable_foreign_key_with_index is null and [...]

The issue? MySQL thinks that it's a good idea to use an index_merge/intersect strategy for query resolution. In this case MySQL would do 2 queries in parallel: one with tenant_id (which uses a valid and good index) and another one with nullable_foreign_key_with_index which is bad, almost a "full table scan in parallel" given that the cardinality of this index is <1000 in a table with >20M rows. More details about this "problem" in here

So, what are the pssible solutions? Given that MySQL "forces" a foreign key to have an index attached:

  1. Drop the foreign key and the index. This is bad, because in the case of a bug in the app we may compromise the referential integrity.

  2. FOREIGN_KEY_CHECKS=0; Drop index; FOREIGN_KEY_CHECKS=1. This is bad, because even that the foreign key still exists, MySQL doesn't validade the column anymore to check if the value actually exists. Is that a bug?

  3. Use query hints in all existing queries to make sure that we are only using the old and efficient "tenant_id_index". This is bad because I have to hunt down all existing queries and also remember to use it again when news queries are built.

So, how can I say: "MySQL, don't bother creating an index for this foreign key, but keep validating it's content in the related table, which is indexed by primary key anyway". Am I missing something? The best idea so far is to remove the foreign key and just believe that the app is working as expected, which probably it is, but this would start a classic discussion about having constraints in APP vs DATABASE. Any ideas?

Upvotes: 4

Views: 4738

Answers (3)

Rick James
Rick James

Reputation: 142208

Summary: Almost always it is better to have a composite index instead of depending on "index merge intersect".

If both columns are tested with = (or IS NULL), it does not matter which order the columns are in the index definition. That is, cardinality is irrelevant.

Upvotes: 0

spencer7593
spencer7593

Reputation: 108370

Q: How can I say: "MySQL, don't bother creating an index for this foreign key, but keep validating it's content in the related table, which is indexed by primary key anyway"

A: No can do. InnoDB requires a suitable index to support the enforcement of foreign key constraint.

Consider the flip side of it... if we are going to DELETE a row in the parent table, then InnoDB needs to check the foreign key constraint.

That means InnoDB needs to check the contents of the child table, to find rows that have a specific value in foreign key column. Essentially equivalent to 

SELECT ... FROM child_table c WHERE c.foreign_key_col = ?

And to do that, InnoDB requires that there be an index on child_table that has foreign_key_col as the leading column.

The options suggested in the question (disabling or dropping the foreign key) will work because then InnoDB isn't going enforce the foreign key. But as noted in the question, what this means is that the foreign key isn't enforced. Which defeats the purpose of the foreign key. The application code could be responsible for enforcing referential integrity, or we could write some ug-gghhh-ly triggers (no, we don't want to go there).

As Gordon already noted in his (as usual excellent) answer... the problem isn't really dropping the index on the foreign key column. The actual problem is the inefficient execution plan. And the most likely fix for that is to make sure a more suitable index is available.

Composite indexes are the way to go. An index like this:

... ON child_table (foreign_key_col,tenant_id,...)

would satisfy the requirement of the foreign key, an index with the foreign key column as a leading column. And drop the (now redundant) index on just the singleton foreign_key_col.

This index could also be used to satisfy the query that's using a horrible index merge access plan. (Verify with EXPLAIN.)

Also, consider adding columns (such as foreign_key_col) to the index that has tenant_id as the leading column 

... ON child_table (tenant_id,...,foreign_key_col,...)

and drop the redundant index on the singleton tenant_id col.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269503

For this query:

from large_table
where tenant_id = ? and
      nullable_foreign_key_with_index is null and [...]

Just add the index large_table(tenant_id, nullable_foreign_key_with_index).

MySQL should use this index for the table.

I'm pretty sure you can do this backwards (I would be 100% sure if the comparison were to anything other than NULL, but I'm pretty sure MySQL does the right thing with NULL as well.)

large_table(nullable_foreign_key_with_index, tenant_id)

And MySQL will recognize that this index works for the foreign key and not create any other index.

Upvotes: 6

Related Questions