Skip to main content

Benjamin Oakes

Photo of Ben Oakes

Hi, I'm Ben Oakes and this is my geek blog. Currently, I'm a Ruby/JavaScript Developer at Liaison. Previously, I was a Developer at Continuity and Hedgeye, a Research Assistant in the Early Social Cognition Lab at Yale University and a student at the University of Iowa. I also organize TechCorridor.io, ICRuby, OpenHack Iowa City, and previously organized NewHaven.rb. I have an amazing wife named Danielle Oakes.

Filtering for the MySQL category. Clear

char vs varchar for UUIDs in MySQL and PostgreSQL

by Ben

I spent some time today evaluating whether switching columns that store UUIDs from varchar(255) to char(36) (or binary, etc) would result in any noticeable performance improvement in MySQL. It does seem like it could make an improvement, but not enough to be worth the effort in our case.

What I learned from researching today:

If I’m wrong about what I took away from reading today, please let me know in the comments. I’d love to learn more about this.

That’s not doing quite what you think…

by Ben

I recently helped an intern at Hedgeye work through a problem with a database query. Because I’m working in a separate timezone, I ended up making suggestions through a GitHub pull request. We discussed and decided that what I wrote was self-contained enough that I should re-post so it can help others.

:conditions => ["event_type != ?", 'LOGIN'||'LOGOUT'],

I don’t think this is doing quite what you think…

'LOGIN' || 'LOGOUT' # => 'LOGIN'

So this turns into:

where event_type != 'LOGIN'

I’m guessing you meant to do:

where event_type != 'LOGIN' or event_type != 'LOGOUT'

But, believe it or not, != is a MySQL proprietary extension to SQL. It would probably be best to use something that’s a part of ANSI SQL:

where event_type <> 'LOGIN' or event_type <> 'LOGOUT'
-- alternative:
where event_type not in ('LOGIN', 'LOGOUT')

Because these are literals (not user-provided values), there’s no point in sanitization using ?.

Conclusion:

:conditions => "event_type not in ('LOGIN', 'LOGOUT')",

database configuration does not specify adapter

by Ben

From an answer I wrote for StackOverflow:

Another possible cause:

In Rails 3.2.x, establish_connection has a default argument set from the environment:

From connection_specification.rb:

def self.establish_connection(spec = ENV["DATABASE_URL"])
  resolver = ConnectionSpecification::Resolver.new spec, configurations
  spec = resolver.spec

The way ConnectionSpecification::Resolver works depends on ENV['DATABASE_URL'] giving a nil if not set. (Normally, it would be something like postgres://...).

So, if you happen to have misconfigured DATABASE_URL such that ENV['DATABASE_URL'] == '', that will give you database configuration does not specify adapter.

MySQL console tip

by Ben

If you terminate statements with \G instead of ;, the MySQL console will print the result set vertically rather than as a horizontal table.

For example, instead of:

+----+--------+--------+
| id | foo_id | bar_id |
+----+--------+--------+
| 1  | 6      | 2      |
+----+--------+--------+

You will have:

*************************** 1. row ***************************
                         id: 186
                     foo_id: 6
                     bar_id: 2

It’s really useful for wide tables, or ones with text columns.

I’m always surprised that the MySQL docs don’t make it easier to find this — it’s one of the better tricks for using the database console.