The MU forums have moved to

Database Structure and Wastefulness? (5 posts)

  1. fogster
    Posted 16 years ago #

    Pre-1.5, the wp_1_comments table had a comment_approved field that was an ENUM, and it would contain "0" (comment not approved), "1" (comment approved), or "spam."

    How is this field <i>supposed</i> to work? Because to me, it seems that it's become a boolean (either a 0 or 1, but no "spam" option), but that the type has also been changed in MySQL to a VARCHAR(20), which seems pretty obnoxious.

    Actually, comment_type seems to be the same: it's either blank (a comment), "pingback," or "trackback." Why is this a 20-character VARCHAR? And why are 100 bytes allocated for the IP!? An IP couldn't possibly exceed 15 bytes.

    Is there some reasoning I'm overlooking in the database structure? Is it safe for me to go in and change the field sizes to more sane values?

  2. donncha
    Key Master
    Posted 16 years ago #

    fogster - it's not safe to change the field types as database upgrades may break. They were changed to be more flexible in the future.

    VARCHAR fields only use as much space as required, with a minimum of 4 bytes AFAIR.

    You should post to the WordPress forum at if you'd like to find out more.

  3. cafespain
    Posted 16 years ago #

    Varchar only uses the amount of space it needs for the data it holds, rather than char which uses the full amount regardless.
    An empty string in a varchar only uses 1 byte of storage, two characters e.g. 'ab' uses 3 bytes and so on...

  4. mjhca
    Posted 16 years ago #

    For efficiency, IP addresses really should be stored as INT(4) UNSIGNED ... MySQL ATON_INET and INET_NTOA should be used to convert between the numeric value, and the actual IP addresses. That way, only 4 bytes are required for storage.

  5. lunabyte
    Posted 16 years ago #

    These are all from the WP core, and as Donncha said, take it up over there.

About this Topic

  • Started 16 years ago by fogster
  • Latest reply from lunabyte