On Jan 25, 2013, at 6:54 AM, Yannick Chevalier wrote: > Hi all, > > I have seen that at line 99 of: > https://github.com/tablatom/hobo/blob/master/hobo_fields/lib/hobo_fields/model.rb > the index created for a polymorphic association is in the order (type, id). > However, > I believe that it would be more efficient to index first with the id, so that > only a few records > would match. > Is it some ActiveRecord voodoo that reorders them on the fly, or is there > other considerations ?
The type is first in the index because some DBs (notably MySQL) only use prefixes of multicolumn indexes. From http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html#idp36594256 : "If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3)." In this case, putting type first means that queries that are selecting against a particular type without an id (for instance SomeSTISubclass.all) will still be able to use the index. Putting id first would make those same calls do a full table scan. --Matt Jones -- You received this message because you are subscribed to the Google Groups "Hobo Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/hobousers?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
