18
Helpful MySQL Tips and General Understandings
I’ve only worked in the Web industry for a handful of years at this point but in that time, I’ve realized there is plenty to learn and adjust to, specifically from a project-to-project basis. One thing, though, that seems to be pretty consistent across the board and that drives me up the wall is inefficiencies due to laziness. Having worked in a team environment for the last few years, I’ve had the opportunity to dive into other projects to meet deadlines, troubleshoot issues, etc., and boy have I encountered some wonderful code that has caused some sever headaches for a few days. So, to help alleviate the headaches, I’ve compiled a short list of commonly encountered inefficiencies that I tend to find on the database side of projects.
Most of the following is scattered around the Web, but I simply wanted to centralize the most useful of the bunch. If you have any of your own useful gems, want to share your frustration drop us a line; we’ll gladly add to the list.
Data Types
A year or so ago I came across this outstanding post that, unfortunately, no longer seems to exist. Thankfully, I’ve compiled my own chart for the more commonly used datatypes. I find this very handy when working on my database schemas.
| Type | Minimum | Maximum |
|---|---|---|
| SIGNED TINYINT | -128 | 127 |
| UNSIGNED TINYINT | 0 | 255 |
| SIGNED SMALLINT | -32768 | 32767 |
| UNSIGNED SMALLINT | 0 | 65535 |
| SIGNED MEDIUMINT | -8388608 | 8388607 |
| UNSIGNED MEDIUMINT | 0 | 16777215 |
| SIGNED INT | -2147483648 | 2147483647 |
| UNSIGNED INT | 0 | 4294967295 |
| SIGNED BIGINT | -9.22E+018 | 9.22E+018 |
| UNSIGNED BIGINT | 0 | 1.84E+019 |
Password Storage
I’m notorious for forgetting required storage sizes for hash values. Listed below is a simple outline for your reference (and ours).
| Algo | Size | Data Type |
|---|---|---|
| md5 | 16 bytes | char(32) |
| sha1 | 20 bytes | char(40) |
| sha256 | 32 bytes | char(64) |
| sha384 | 48 bytes | char(96) |
| sha512 | 64 bytes | char(128) |
IP Storage
Typically I see all IP information stored in a VARCHAR(15). This works just fine, however, let’s say your client asks you to gather some reports on an specific IP – maybe they are asking for a subnet count or even an IP range lookup. This could get messy depending on the database size. I will leave out the specifics here and direct you to bafford.com for a quality read on the details and breakdown of storing an IP as an UNSIGNED INT as opposed to a VARCHAR(15). All I can recommend is: utilize INET functions; integers are much easier to work with and there is a significant performance boost. The following snippet is the basic usage (from dev.mysql.com’s resources):
INSERT INTO users (ip) VALUES (INET_ATON('209.207.224.40'));
SELECT * AS ip
FROM users
WHERE ip = INET_ATON('209.207.224.40')
32 Tips
Below is a great link that contains some easy and very ideal tips on ways to improve your Queries. I would like to point out specifically the following: Char vs Varchar (tip #9), Table ordering (tip #12), Session data management (tip #16), Caching (tips #21 & #22, Mass inserts (tip #23)
http://www.ajaxline.com/32-tips-to-speed-up-your-mysql-queries
I hope the former proves to be helpful for anyone who is looking for little ways to improve their database performance.