binaryslate

Seems slashdot is all commented out

Last night we crossed over 16,777,216 comments in the database. The wise amongst you might note that this number is 2^24, or in MySQLese an unsigned mediumint. Unfortunately, like 5 years ago we changed our primary keys in the comment table to unsigned int (32 bits, or 4.1 billion) but neglected to change the index that handles parents. We’re awesome! Fixing is a simple ALTER TABLE statement… but on a table that is 16 million rows long, our system will take 3 hours to do it, during which time there can be no posting. So today, we’re disabling threading and will enable it again later tonight. Sorry for the inconvenience. We shall flog ourselves appropriately.

Had this happen before, only on an 16bit integer though, sadly not as impressive as 16 million…

Slashdot

Always on my daily reads is Xaprb, the fella is a genius with mysql. There is always something highly informative to read and always useful. Today’s offering is the MySQL Query Profiler, a perl program to help analyse and improve your queries.

I’ll be giving it a whirl shortly, i’m always on the lookout for ways to improve query time.

You should all read the site everyday anyway if you use mysql on a regular basis. I can’t recommend a better source apart from mysql.com (obviously)

Always on the lookout for smart tips to shrink my mysql db, spotted a great link on storing an IP address.

MySQL has two built-in functions: INET_ATON() and INET_NTOA(). They are actually based on the equivalent inet_aton() and inet_ntoa() which are C library functions present on pretty much every TCP/IP capable system. Why? These two functions are used allover the place in any TCP/IP stack implementation or even application.
The INET_ATON() function converts Internet addresses from the numbers-and-dots notation into a 32-bit unsigned integer, and INET_NTOA() does the opposite. Isn’t that handy!

I shall be implementing this as soon as, i’ve got a lot of IP’s in the db and this could reduce the size a lot.