CrazyEngineers
CrazyEngineers

  • Performance of VARCHAR Index in MySQL

    Kaustubh Katdare

    Administrator

    Updated: Oct 26, 2024
    Views: 1.5K

    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. 

    0
    Replies
Howdy guest!
Dear guest, you must be logged-in to participate on CrazyEngineers. We would love to have you as a member of our community. Consider creating an account or login.
Replies

  • Kaustubh Katdare

    Administrator • Feb 13, 2019

    While you're online - could you let me know your thoughts, #-Link-Snipped-# ?

    Are you sure? This action cannot be undone.
    Cancel

  • Prasad Ajinkya

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

    Are you sure? This action cannot be undone.
    Cancel

  • Kaustubh Katdare

    Administrator • Feb 13, 2019

    Thank you #-Link-Snipped-# . 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. 

    Are you sure? This action cannot be undone.
    Cancel

  • Prasad Ajinkya

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


    Are you sure? This action cannot be undone.
    Cancel
Home Channels Search Login Register