Databases
StackOverflow2013
Postsdbo
POClustered indexes, mysql and Rails
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POClustered indexes, mysql and Rails
    primarykey
    data
    text
    <p>I'm helping with a Rails application, the intent is for that application to be multi-tenanted. What this means is that there will be data from multiple users/organisations in the database tables, and often the access path will be along the lines of "get me all the data for my organisation".</p> <p>We're using MYSQL as the database.</p> <p>Rails by default creates a primary key on the table using the id column. The id column is auto-incremented. This is nice in some ways - rows are always added at the end of the table. However, consider the following situation:</p> <ul> <li>An object called foo. A foo has an id, and always has an organisation_id</li> <li>Over time each organisation creates foos in the database, these foos are interleaved throughout the table (they are stored in id sequence)</li> <li>A use case that involves listing all foos for this organisation</li> </ul> <p>The problem I have is that the foos for an organisation are not located closely together in the database, in fact they're spread around very sub-optimally. Ideally I'd create a primary key of (organisation_id, id) on the table, which would result in all foos for a given organisation being side by side in the table. </p> <p>Unfortunately, when I do this Rails gives me an 'Unknown primary key for table foos in model Foo' error. I think I could deal with this by using the composite keys gem to rails, but it seems like there should be some way to make this transparent at the database level.</p> <p>Is there an alternate approach?</p> <p>For reference, the command on the database to change my index was:</p> <p>ALTER TABLE foos ADD KEY (id); # needed because the id column is auto-increment</p> <p>ALTER TABLE foos DROP PRIMARY KEY, ADD PRIMARY KEY(organisation_id, id);</p> <p>EDIT 1: A blog post that indicates success doing exactly this with composite_primary_keys gem. Which gives me a bit more confidence with that approach, problem is that it's from 2008, so things may have moved on. <a href="http://www.joehruska.com/?p=6" rel="nofollow">http://www.joehruska.com/?p=6</a></p> <p>EDIT 2: Another option I was considering was partitioning instead - the number of organisations probably wouldn't exceed the maximum partitions, and I could probably group them a bit without losing too much benefit. Unfortunately, the key quote is <em>every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key</em> - from the MYSQL manual <a href="http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-partitioning-keys-unique-keys.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-partitioning-keys-unique-keys.html</a>.</p> <p>So I'm still back needing a composite primary key again. I'm a little surprised that Rails cares so much about the primary key, rather than simply that a key is present. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPaulL
    1. USPaulL
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI'm sorry I don't see how composite_primary_keys not transparent at the database level and how does it relate to the `alter table` thing?
      singulars
      1. POClustered indexes, mysql and Rails
      1. USDean Winchester
    2. COsorry, I mean the other way round. I'd like something that is transparent to the Rails app, rather than looking for something that is transparent to the database. My usual aim when tuning for performance at the database level is to make changes that the application layer is unaware of - adding indexes, changing optimiser parameters. I found a blog post that indicates success with the composite_primary_keys gem (which I'll add above), but I'd still prefer to avoid the potential change I'll get to my app by moving to composite_primary_keys - arguably it could break upgrades (to rails 4).
      singulars
      1. POClustered indexes, mysql and Rails
      1. USPaulL
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload