CODEWITHSUNDEEP
mysqlinnodb
Cedric
Cedric

Reputation: 5303

Have an Index against a varchar(6000), or a md5 sum (for comparison with the whole value, not just part of it)?

Using MySQL, I have to compare new values to rows on a column code that is varchar(6000).

- - - - - - - - - - - - - - - - - - - - - - - - - - - - --
| id (int(11))| code     (varchar(6000))                 | 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - --
| 1           | alpha : function bar(1)                  |
| 2           | dog.hasFourLegs()                        |
 ...

I check if a new input code is equal to the one in the database, 'frequently' (once every 10 minutes). I can obviously have a new column with a md5 sum of the code, and compare md5 sums, and index this md5_code . But isn't indexing the code varchar(6000) more efficient ? (efficient in time, and in storage space). The table will have about 300 000 rows in the long term. Innodb used.

Extra question : is there a big difference of behaviour between innodb and mysam for this problem ?

Upvotes: 2

Views: 474

Answers (1)

Oz Solomon
Oz Solomon

Reputation: 3044

Depending on the actual data you have, Prefix Indexes may be the solution for you.

MySQL lets you set up an index that only looks at the first N characters of the field. For example, to index the first 20 characters only:

ALTER TABLE mytable ADD KEY(code(20));

How many characters to index depends on your data. For example, if the strings in all your records start with the same 3 letters then having a prefix index of 3 won't help you at all. You need to scan your data to determine the right index length for that data set.

There are some tips for choosing the prefix length in this Percona Best Practices presentation (search for Choosing Prefix Length).

Upvotes: 1

Related Questions