Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

Autogenerated fixture ids too long for some MySQL integer types

#6638

We've came across this by accident. Some of our tables had the primary key column "id" set to SMALLINT (http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html) to save a little disk space. The problem is that the ids generated by Fixtures::identify are too long - they don't take the length of the target column into account, being just a modulo'd hash of the name.

This is on Rails 2.3.11.

The question is, is this something warranting attention ?
I'd like to go and try to make a test and a fix for this, as I consider this a bug - the magic fixtures should be magic enough to work on smaller columns.

But will a solution, which might make the Fixture system more complicated, be accepted ? Or is this too much of an edge case ?

Reported by Martin Tepper · March 28th, 2011 @ 01:59 PM

State: open
Milestone: 3.1
Assigned to: Andrew White Andrew White
Importance: Low

Activity

  1. Andrew White
    Andrew White
    • Importance changed from to Low

    Wouldn't it be easier just to convert the columns to INT? What's the justification for keeping them as SMALLINT - performance difference would be next to nothing and at most you'd save 130KB before you overflowed the id column. I can see the logic of keeping other columns as small as possible if you're dealing with millions of rows but not for the primary key column.

    March 28th, 2011 @ 09:33 PM

  2. Martin Tepper
    Martin Tepper

    Well, the justification is that if you reference the primary id from other tables, and if those have a large number of rows, the few bytes per foreign key entry can add up. Especially if you have (multiple) indices on the foreign keys.
    This will probably not be a game-changer for one table, but when you are in a tight spot memory-wise on your DB server, you're trying to optimize where you can.

    March 29th, 2011 @ 04:21 PM

  3. Andrew White
    Andrew White
    • State changed from new to open
    • Milestone set to 3.1
    • Assigned user set to Andrew White

    Yep, I can see that would make a difference - go ahead and see if you can fix it.

    March 29th, 2011 @ 04:27 PM