#69 ✓resolved
Mathijs Kwik

database indexes

Reported by Mathijs Kwik | July 29th, 2008 @ 01:32 AM | in Beyond Hobo 1.0

Hobofields should have sane defaults for putting indexes on certain fields. Fields for associations (_id) and fields that is searched for/sorted on (name, title) are easy candidates.

Comments and changes to this ticket

  • Tom Locke

    Tom Locke July 29th, 2008 @ 09:28 AM

    • Milestone cleared.

    I'm not sure if you can make these assumptions safely can you?

    E.g. if the average frequency of IDs in a foreign key column is less than 1, doesn't an index make things worse?

    Also, does as index help with searching and sorting?

    As you can probably tell I'm not a DB expert!

    I'll take the milestone off until we agree if this can work reliably or not.

  • Tom Locke

    Tom Locke July 29th, 2008 @ 10:55 AM

    Setting this to 'new' until we decide it's in

  • Tom Locke

    Tom Locke July 29th, 2008 @ 11:12 AM

    • State changed from “new” to “investigating”
  • Mathijs Kwik

    Mathijs Kwik July 29th, 2008 @ 07:37 PM

    http://www.websitedatabases.com/...

    As you can see, indexes work well on values that differ a lot (like foreign keys, titles and stuff) and not for stuff like status(string) or booleans.

    I think you can think of an index as a ordered list containing the values a column has to offer.

    My experience learned me to put indexes on every foreign key(primary key(id) is indexed automatically. Furthermore (didn't do performance-testing on those) stuff you order by a lot will usually speed up when indexed, because then the ordering is already done in advance.

    So depending on what data/table you are looking at, this is usually title/name or created_at

    The article I linked mentions indexes can slow down queries on small tables, I didn't check this, but on small tables everything feels instantly anyway.

    Maybe we should offer indexing certain columns as an option.

    Of course this should be selectable manually, but maybe ./script/generate hobo_indexes would be nice too, just to generate a migration that reflects the current state of foreign keys and default-sort options models have atm.

  • Owen

    Owen July 31st, 2008 @ 10:41 PM

    I like Mathijs' idea of an optional

    ./script/Generate hobo_indexes.

    All foreign keys by default. Others by request. This is worth a bigger discussion.

  • Tom Locke

    Tom Locke August 5th, 2008 @ 11:40 AM

    • State changed from “investigating” to “open”
    • Tag changed from hobofields, migrations to enhancement, hobofields, migrations
    • Milestone set to Beyond Hobo 1.0

    +1 to do this as a generator

  • Matt Jones

    Matt Jones November 15th, 2009 @ 09:25 PM

    • State changed from “open” to “resolved”

    This is in edge now.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

People watching this ticket

Pages