SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to determine the index size

    Hi,

    I must search on a tinytext field. I want to put an index on it to make it faster. but i have to specify the size for that index.

    MySql says this: "....Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters...."

    Ok so how do i calculate the number of bytes needed to get a good index on a tinytext field knowing that smaller is better? My Character set is in UTF-8.

    Tanks

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    are you sure you want a standard index? with a standard index you would only be able to search for left-anchored text.

    maybe you really want a fulltext index. see http://www.sitepoint.com/forums/showthread.php?t=540471

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, well i want an exact match of a given string...

    Please let me know!

    Thanks

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    how long will most of the strings be?

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The very max would be 50 characters.

    It's the part of an URL that i stored in DB. Most of them are between 20-30 characters long.

    Thanks

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    if that's true, then tinytext is a poor choice of data type. use varchar instead.

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, tinytext was not the best choice. But still, if you are not sure about the maxlenght, how do you determine the index size that is needed knowing that smaller is better?

    Thank you for taking the time to answer me.

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    in a perfect world, you would want the smallest index prefix size that results in the highest cardinality. sometimes your index gets too big to be useful (i.e., it keeps pushing other indexes out of memory) so reducing your index prefix will help. i don't have any good rule-of-thumb for this particular situation, but the main factor i take in to consideration is the amount of memory the server has available, the amount of data the server will store in the foreseeable future, and how critical it is that queries on this table be efficient.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •