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 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 July 29th, 2008 @ 11:12 AM
- State changed from new to investigating
-
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 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 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 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.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป