Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query, below is the structure of the table:

CREATE TABLE `jos_tag_rules_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `label` varchar(255) NOT NULL DEFAULT '',
  `user_updatable` tinyint(1) NOT NULL DEFAULT '1',
  `description` text,
  PRIMARY KEY (`id`),
  KEY `idx_name_jos_tag_rules_fields` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

We have only 34 records in this table and there are one index on column name. I used this table in a query with joining on other tables and checked the query plan which is below:

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "jtrf"  "ref"   "PRIMARY,idx_name_jos_tag_rules_fields" "idx_name_jos_tag_rules_fields" "767"   "const" "1" "Using where; Using index; Using temporary; Using filesort"
"1" "SIMPLE"    "jtrv"  "ref"   "PRIMARY,idx_field_id_jos_tag_rules_values" "idx_field_id_jos_tag_rules_values" "4" "jprod.jtrf.id" "14"    "Using where"
"1" "SIMPLE"    "jct"   "ref"   "PRIMARY,idx_content_id_jos_content_tags,idx_tag_value_id_jos_content_tags" "idx_tag_value_id_jos_content_tags" "4" "jprod.jtrv.id" "123"   "Using index"
"1" "SIMPLE"    "jc"    "eq_ref"    "PRIMARY,idx_access,idx_id_jos_content" "PRIMARY"   "4" "jprod.jct.content_id"  "1" "Using where"

As you can see first record, idx_name_jos_tag_rules_fields is getting used as key as well as key_len is 767. Since, nameis a varcahr type field and its size is 255. I checked max length of this column which is less than 100. Then, why key_len is 767. I have read few posts about key_len. Example link. But, in my case, it should be 100 or 255. I am unable to understand the reason behind using so high key_len.

Also, as per the acceptable answer provided, smaller key_len requires less disk space and makes the query execution slower. So, What is the role key_len in query performance? How small and large key_len affect the performance? Please let me know if any further information required. Many Thanks...

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.