#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    14
    Rep Power
    0

    Question Arbitrary precision in mysql


    I just learned that the BIGINT data type doesn't go on forever. It only has an 8-byte range.

    Is there any way of storing arbitrarily large numbers in MYSQL without having to resort to strings? I'm sure it would really suck on performance if 1208925819614629174706176 required 25 bytes (the length of the string) instead of just ten bytes if it were stored as a number.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by bonecone
    I'm sure it would really suck on performance if ...
    depends

    what do you need to know for?

    are you really doing calculations on that size number?

    or are you thinking more of space considerations instead of performance?

    as for performance, have you tested and timed it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2008
    Posts
    14
    Rep Power
    0
    Originally Posted by r937
    depends

    what do you need to know for?

    are you really doing calculations on that size number?

    or are you thinking more of space considerations instead of performance?

    as for performance, have you tested and timed it?
    It's meant to be a compact way of storing the paths for nested categories. I'm building a general-purpose database application in PHP, kind of like the SOBI PRO component for Joomla but way more advanced.

    Each category in the database must have its own full path stored with it, not just the id of its parent category. I've set it up this way so that I could define a category structure once and then re-use it as many times as needed, so each instance of the category structure has to be uniquely identified by its path.

    I'm designing it for any amount of data. I want to use it as the back-end of a search engine, and I figure that when your database gets big enough the size of the data-types become a factor in performance as well.

    Am I right in assuming that I'm stuck with using text?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by bonecone
    Am I right in assuming that I'm stuck with using text?
    no

    you could use an INTEGER column for all fake-strings-as-numbers that fit, then use NULL in that column and store your fake-strings-as-numbers in an adjacent BIGINT column, and then if they're too long for that, use NULL and store the actual strings in a VARCHAR

    of course, you would then use COALESCE to pull out the one non-NULL value in the three columns

    this way you can really collapse your row size by a meaningful number of bytes, like maybe five or six

    and i trust your sarcasm detector is in good working order

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo