2

Explanation:

I need to do a lot of searches for 64 character Strings (varchar 64) in a MySQL database table. this might happen in a while/for loop of 1000 times each time (obv different strings each time):

Example:

// Every 2 hours:
for($i=1;$i<1000;$i++){...

SELECT id FROM table WHERE string1='$string1' AND string2='$string2'

string1 column is a unique varchar(64) , and string2 is a not unique varchar(35)

This InnoDB table might have 1-10 million rows over time.

Questions:

  1. Is it enough for performance to index the table (joint indexing string1 and string2 together)?

  2. Is it better for MySQL search performance If I convert these varchar(64) strings to large integers based on their characters? (I was thinking Length will be larger ofcource, but it will be Integer and better for search)

  3. If converting them is better which algorithm should I use to convert them so that string1 would remain Unique?

  4. Any better Ideas?

Improve this question
8
  • if you choose to stick with characters, then char is faster than varchar; perhaps as much as 20% more so. Commented Aug 3, 2015 at 0:43
  • as always benchmark each approach see which is faster Commented Aug 3, 2015 at 0:43
  • If the strings are always going to be exactly 64 characters, you should use char(64). A little more efficient. Commented Aug 3, 2015 at 0:44
  • @RobertCathey the first one is always 64 characters and unique, the second is not its 25-35 characters, Would it be more efficient if I use cha(64) for the first one and the second still remain varchar(32)? Commented Aug 3, 2015 at 0:46
  • 1
    @Ara yes char() is more efficient when the character count is always going to be the same, because the data is stored differently in varchar and char. varchar flexibly allocates space within a certain range of what the saved text is. char() allocates the exact amount that you request, so if you created char(64) and had a string that was 32 characters then it would still use 64 bytes to create the string, that's when varchar is more efficient. Commented Aug 3, 2015 at 0:52

2 Answers 2

Reset to default
3

This sounds like a premature optimization.

If your preferred data types are varchar(64) and varchar(35), then start with those. An index on (string1, string2) should be sufficient for most purposes.

The idea of storing the strings as char(64) (and perhaps char(35)) is a good idea. However, this requires that all strings be the same length. You could also start playing around with binary. This has the advantage that the comparisons do not need to take character encodings and collations into account.

Another possibility is to consider a hash index/adaptive hash index rather than a b-tree index. The comparisons using a hash index should be faster; however, they are more limited than b-tree indexes.

However, my guess is that a simple index on the two columns should suffice, and performance enhancement efforts can be spent in other areas.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You may create a combined index with string1 and string2.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.