Bug #1179 Forcing indexes for foreign keys (InnoDB)
Submitted: 2 Sep 2003 8:08 Modified: 30 Oct 2004 9:11
Reporter: Van Panyanouvong Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:4.0.12 OS:Windows (Windows, AIX)
Assigned to: CPU Architecture:Any

[2 Sep 2003 8:08] Van Panyanouvong
Description:
I would like to see the index for foreign keys automatically generated.  In addition, unless InnoDB handles the following case better than Oracle, indexes for foreign keys should be optional.

How to repeat:
Problem Example:

Table: orders (order_id, creator, status_cd)
Size: 7 million
Assumptions:  creator is fairly unique, status_cd has 7 possible values

Consider the following SQL:

SELECT * FROM orders WHERE creator = 1 AND status_cd = 'NEW'

Even though the first filter has narrowed down the results to just a few values, hitting the index for "status_cd" would result in an average scan of one million values.

Had "status_cd" not been indexed, only the few rows returned from the first filter would've been scanned.

Suggested fix:
Indexes for foreign keys should be optional.
[30 Oct 2004 9:11] Heikki Tuuri
Hi!

MySQL-4.1 now automatically creates the index on the FOREIGN KEY.

However, it does not create an index on the REFERENCED key. That is not a big problem, because in most cases the referenced key is the PRIMARY KEY of the referenced table.

Best regards,

Heikki