How should I index this MySQL db?

Question

I am creating a site that allows users to share specific pages to the public. It is similar to how jsbin.com let's you create a public url of the script you're working on. The basic MySQL table I am working with now is:

CREATE TABLE IF NOT EXISTS `lists` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `hash` varchar(6) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash` (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The key field is the column that holds the random string the user will type at the end of the URL. So, for example, if the user publicized there page with the hash value of a1b2c3 then the URL would be http://mysite.com/a1b2c3

OK, sorry the description took so long. My question is, should I index the hash column? Will this make queries faster, as I am primarily going to look rows up by their hash value?

Also, I have another table that has a foreign key relationship to this one. Does it make sense performance-wise to relate it to the hash column?

Thanks for the help.

Answer 1

Yes, if you are looking up by the hash, then you most certainly would want to have an index on that field. By the looks of your table design, you would probably want it to be a unique index as well.

Answer 2

Yes, you definitely should index the hash column and, which is very important, disable the key compression.

The key compression is a feature in MyISAM indexes that shrinks the size of a text index by storing only the length of the common prefix in the index keys.

This may help to improve performance on the human language phrases, but is not going to help for the hashes.

This article from my blog that compares performance of index lookups for the hash-like strings with key compression on and off:

• Index search time depends on the value being searched

To disable key compression, add PACK_KEYS = 0 to the CREATE TABLE statement.

Update:

This statement in your CREATE TABLE:

UNIQUE KEY `hash` (`hash`)

effectively creates a UNIQUE index on hash, so you already have it. Just make sure that the key compression is off.

Answer 3

Not only should you index on the hash column, you should probably make it the primary key and get rid of the redundant id column.

Are there some other tables and/or columns you didn't list here? This table has little point outside of the created_at column.

Answer 4

Yes. It's important to add UNIQUE INDEX for that column. First of all you have to be sure that it's unique (well... it's kind of ID) and you'll do a lot of queries like following:

SELECT .... FROM .... WHERE hash = 'abc323';