View Feed
group-icon
Coffee Room
Discuss anything here - everything that you wish to discuss with fellow engineers.
12833 Members
Join this group to post and comment.
Kaustubh Katdare
Kaustubh Katdare • Feb 12, 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. 

Kaustubh Katdare
Kaustubh Katdare • Feb 13, 2019

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. 

Kaustubh Katdare
Kaustubh Katdare • Feb 13, 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. 

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 -