How should I index this MySQL db?

Question

I am creating a website that allows users to publish individual pages. This is similar to how jsbin.com allows you to create a public script URL that you are working on. The base MySQL table I'm working with now:

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 a column that contains a random string that the user will print at the end of the URL. So, for example, if a user posted a page with the hash value a1b2c3 , then the URL would be http://mysite.com/a1b2c3

OK, sorry that the description took so long. My question is: should I index the hash column? Will this make queries faster since I'm going to look up the strings by their hash value first?

In addition, I have another table that has a foreign key relationship to this. Does it make sense to work with it to associate it with a hash column?

Thanks for the help.

Answer 1

Not only should you index the hash column, perhaps you should make it the primary key and get rid of the excess id column.

Are there any other tables and / or columns that you did not specify here? This table has a small dot outside the created_at column.

Answer 2

Yes, if you are viewing a hash, you will probably want to have an index in this field. In appearance of your table design, you probably want it to be also a unique index.

Answer 3

Yes, you should definitely index the hash column and, very importantly, disable key compression.

Key compression is a function in MyISAM indexes that reduces the size of a text index, keeping only the length of the common prefix in the index keys.

This can help improve performance on human language phrases, but does not help hashes.

This article is from my blog, which compares indexing metrics for hash-like strings with key on and off:

• Index search time depends on the search value

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 one. Just make sure key compression is turned off.

Answer 4

Yes. It is important to add UNIQUE INDEX for this column. First of all, you must be sure that it is unique (well ... this is a kind of identifier), and you will make many requests, for example:

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