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.Posted in: #Coffee Room
While you're online - could you let me know your thoughts, @Prasad Ajinkya ?
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.
Thank you @Prasad Ajinkya . I'm not sure how feasible it'd be to use NoSQL only for handing specific data. I'm wondering if Redis could be used.
Isn't Redis dependent on your memory size? Can all your records be stored? You could (and perhaps should) use it as a cache.