I'm developing a site that enables customers to talk about specific pages towards the public. It is comparable to how jsbin.com let us you produce a public link to the script you are focusing on. The fundamental MySQL table I'm dealing with now's:

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 area may be the column store the random string the consumer will type in the finish from the URL. So, for instance, when the user promoted there page using the hash worth of a1b2c3 then your URL could be http://mysite.com/a1b2c3

OK, sorry the description required such a long time. My real question is, must i index the hash column? Will this make queries faster, like me mainly likely to look rows up by their hash value?

Also, I've another table which has a foreign key relationship to that one. Will it seem sensible performance-smart to relate it towards the hash column?

Just help.

Not just in the event you index around the hash column, you need to most likely allow it to be the main key and eliminate the redundant id column.

Exist another tables and/or posts you did not list here? This table has little point outdoors from the created_at column.

Yes, if you're searching for through the hash, then you definitely most definitely may wish to come with an index on that area. Through the looks of the table design, you'd most likely want that it is a unique index too.

Yes, you certainly should index the hash column and, which is essential, disable the important thing compression.

The important thing compression is really a feature in MyISAM indexes that reduces how big a text index by storing only the size of the most popular prefix within the index secrets.

This might help to improve performance around the human language phrases, but won't help for that hashes.

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

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

Update:

This statement inside your CREATE TABLE:

UNIQUE KEY `hash` (`hash`)

effectively produces a UNIQUE index on hash, so you have it. Just make certain the key compression is off.

Yes. You need to add UNIQUE INDEX for your column. To begin with you've to be certain it's unique (well... it's type of ID) and you will perform a large amount of queries like following:

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