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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Our MySQL database will contain an encyclopedia. The encyclopedia will be shown in pages where every page contains entries starting from a letter.

Which indexes should I use? Should I have two indexes for the field "title" (one index of the length 255 for ordering and one index of the length 1 for indexing by the first letter)?

CREATE TABLE `cyclopedy` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(255) collate utf8_bin NOT NULL,
  `article` mediumtext collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `title` (`title`)

Well, now I understand that length 1 is not enough for indexing by the first letter, because an UTF-8 letter may consist of three bytes.

share|improve this question
up vote 3 down vote accepted

An index can seek by a subset of characters, as long as you're searching from the left. E.g., "Inter%" can seek, "%net" will not.

However, the first character is not necessarily the character under which the article would be sorted. "The Internet" should go under "I", not "T". You probably need two fields, DisplayTitle and SortTitle; a single-character index on the latter may be worthwhile.

share|improve this answer

Your Answer


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

Not the answer you're looking for? Browse other questions tagged or ask your own question.