CODEWITHSUNDEEP
mysqldatabaseperformancesyntaxforeign-keys
Todd
Todd

Reputation: 3103

What does a number in parenthesis after a column declared in a MySQL table's foreign key represent?

I came across a MySQL (5.0.45-community-log) table the other day that produced a key as follows after printing a "SHOW CREATE TABLE " statement:

KEY `idx_object_relation_3` (`object_id`,`object_type`(4),`status`,`user_type_id`,`created_datetime`)

Could anyone explain to me what the "(4)" does in that KEY definition? Does it limit the number of characters retrieved from "object_type" sort of how the '1' parameter for TINYINT(1) would function?

The greater problem Im facing is that this table is copied to a few databases across different environments (such as development, staging, testing, production, etc), and in only one of these environments is this KEY defined. Attempting to drop the key resulted in nothing happening for a few hours, then a lost connection. Performance in just this one environment, regarding this table, is so slow that it is unusable (though the table has 5,453,757 records). Actions involving this table in environments where this KEY is not defined operate at acceptable speeds. There is no other difference between environments/databases regarding this table.

Upvotes: 1

Views: 410

Answers (1)

tadman
tadman

Reputation: 211590

Normally a hint like (4) indicates that only the first 4 letters of that field are indexed. Sometimes fields are way too long to index in their entirety so you need to focus on a subset.

This has enormous consequences on UNIQUE type indexes where that will only enforce uniqueness on the first N characters. If you're just concerned about order this is an optimization trick.

Upvotes: 1

Related Questions