Mar
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.

-- Collin

Feb
17

That which is typically unnoticed: Easter eggs with jQuery and 960.gs

I love digging around sites’ code and Google Chrome has made it even more of a habit. I’ve learned quite a bit just by doing said digging. That being said, we tried to incorporate some similar type “Easter Eggs” on the re-launch and re-focus of Collective Colony.

One of the first things we did was setup a simple layout grid that followed the 960 gs. Used for development purposes, as well as eye candy, it was a very straightforward and simple setup. Below is a simple snippet of the bulk of the work:

// create div, make sure it's hidden, prepend it to body
var gridElem = $('<div id="' + elemId + '"><div>`Esc`</div></div>');
var pageHeight = parseFloat($(document).height());
gridElem.height(pageHeight);
gridElem.css('display', 'none');

Once this has been “prepended” to the body, you can simply attach an event handler for the click event and use a slideIn/Out() transition.

Another “egg” that sort of came after the fact was a simple random projects sort / swap. The one tricky part to this “sort” was carrying the position as the elements shifted. This was easily resolved with two simple arrays. The first simple maps element locations, while the other maps the original states. Seen below is, again, the bulk of the work:

// Array - holds all original positions
var projectListPositionState = [];
var listKeys = [0, 1, 2, 3, 4];

// Sort positions
listKeys.sort(function() { return 0.5 - Math.random(); })

// Calculate movement and animate
projectList.each(function(key, item) {
    var movement = projectListPositionState[listKeys[key]] - projectListPositionState[key];
    $(this).stop().animate({"left": movement}, 2000);
});

I’m sure there may be a jQuery plugin that will do something very similar but I wasn’t too keen on adding another plugin thus having the server dish out another file of xyz size. Simple and efficient is what we were going for. Plus there’s nothing like the satisfaction of doing it yourself.

If you want to see either of these in action, simply click the gray box or green box in the header of our home page. The gray will display the grid, while clicking the green box will re-arrange the images in our projects section farther down the page.

-- Collin

posts by

collin