# Mysql – Are two indexes needed

indexindex-tuninginnodbMySQLsorting

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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

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.

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, but most likely a full-length index will be just fine.