Performance of VARCHAR Index in MySQL
I'm exploring the possibility of adding index on a fixed-length VARCHAR column on table in MySQL database I'm working on. This table is expected to grow big very quickly [over 10 million rows].
I've a few questions -
1. Is it true that integer (or numeric?) indexes are considerably faster than non-numeric ones?
2. Would it be wiser to convert the string to be added into a hash (MD5) and then store it as a Hash Index?
3. Or should I find out a way to convert the textual string into a unique number first and then index it?
PS: I'm not very familiar with Indexing.
If you are thinking of using Hash Indexes, then why not work with a system that's built for it (Mongo? Couch?)
Hash Indexes wont allow you to do comparative checks (<, >, etc). Keep that in mind and design. At 10m rows, I'd want to think about these issues.
Isn't Redis dependent on your memory size? Can all your records be stored? You could (and perhaps should) use it as a cache.
Only logged in users can reply.