12 Feb 2019

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. 

13 Feb 2019

While you're online - could you let me know your thoughts, @Prasad Ajinkya ?

Prasad Ajinkya

Prasad Ajinkya

Computer Science
13 Feb 2019

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. 

13 Feb 2019

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. 

Prasad Ajinkya

Prasad Ajinkya

Computer Science
13 Feb 2019

Isn't Redis dependent on your memory size? Can all your records be stored? You could (and perhaps should) use it as a cache.


Share this content on your social channels -

Only logged in users can reply.