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
add comment

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.