Take the 2-minute tour ×
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. It's 100% free, no registration required.

I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in the IN ('...') list is going to be approx 100. The table will likely have millions of rows after the first few months. I understand that indexing this could slow down inserts and could create quite a large index.

QUESTIONS

  1. Would it be faster to store a hash of the value as well and instead index and search on that?
  2. Does that even make sense if the values are not guaranteed to be unique?
  3. If hashing the values gives them a consistent length, would indexing that make queries faster?

I am running mysql 5.1 and using INNODB engine.

share|improve this question
    
Did you consider creating a hash index on your varchar column instead of traditional b-tree index? –  a1ex07 May 2 '13 at 15:14
add comment

1 Answer

What you are asking is a little daunting. Here is why:

Would it be faster to store a hash of the value as well and instead index and search on that?

Creating a hash column and indexing sounds like a great idea. I have suggested that back on March 03, 2013 : Possible INDEX on a VARCHAR field in MySql (See Suggestion #3)

Does that even make sense if the values are not guaranteed to be unique?

This would depend on the cardinality of that hash column. Since you said you will have millions of rows, let me express this in numerical terms:

Run SELECT COUNT(DISTINCT hashcolumn) ... against the table. For a one million row table, this count should be greater that 20. In other words, each distinct value should have no more that 50,000 rows (5% of the table rows). Any value that has more that 50,000 rows will cause the MySQL Query Optimizer to dismiss the index from being used and make a full table scan the preferred method for that hash value.

If hashing the values gives them a consistent length, would indexing that make queries faster?

I would say Yes and Perhaps at the same time. Why two answers? Indexing and using a hash column in place of a long column sounds brilliant against a MyISAM Table. You said you are using InnoDB.

When it comes to using Fixed vs Variable text, I would go with MyISAM over InnoDB

EPILOGUE

If the table is fairly-to-heavily used in Transactions, the table must stay InnoDB. You can take better advantage of your idea in MyISAM. You can go forward with the hash idea. Please make sure the PRIMARY KEY is single integer column (BIGINT if you know you will exceed 2 billion rows. Otherwise, INT). I would do a major RAM upgrade and increase the InnoDB Buffer Pool size accordingly.

share|improve this answer
add comment

Your Answer

 
discard

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.