/tmp lesson learned the hard way

As my friend Max so elegantly said when I  made my first big fuck up “most programs make it so you can’t shoot yourself in the foot too badly, working in the terminal there’s no such thing”

So for reasons I can’t figure out, I had to save files to the tmp folder when connected to my company’s database server. Which, w/e makes no different to me.

Until I waited 2 patient days for a query to finish running then restarted my computer…

turns out /tmp is nothing like Desktop/temp and everything gets deleted when you shut down.

the bright side- Having no alternatives but to make the query run faster I learned the black magic that is indexing.

Having only an average understanding of database engineering, I thought indexes worked differently than they actually do.

What i thought they did- Act like a table of contents in a book. So if i only need my query to pull stuff out of Chapter 3, it prevents it from looking through the whole book. This makes total sense if you have repeated data like male/female etc. But what if every record is unique? What’s the point of indexing on, say, the primary key?

What it actually does- Using my book analogy again, indexes can also be a table of contents filled with just page numbers, so it can jump right to the page without scanning the entire book for it.

The query then ran in 16 seconds. Which made me decide it’s sorcery…

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s