Re: [Radiant] DB Indexes
But yes, these are, in retrospect, so "obvious". Can we merge them into the core? http://is.gd/a2J9 Sean ___ Radiant mailing list Post: Radiant@radiantcms.org Search: http://radiantcms.org/mailing-list/search/ Site: http://lists.radiantcms.org/mailman/listinfo/radiant
Re: [Radiant] DB Indexes
Sean Cribbs said the following on 12/03/2008 09:46 AM: > I've applied Yevgeny's indexes (without the uniqueness constraints - > YAGNI) and here's some circumstantial (i.e. not rigorous) results from > running the spec suite: > BEFORE ... > --- > real 137.84 > user 58.80 > sys 9.15 > > AFTER [speedup] ... > --- > real 87.59 > user 60.00 > sys 8.78 > > [...] -- note that the user and > sys didn't change much. Thanks, Yevgeny! I've applied the indexes to a number of hosted sites and the user response is that they "feel a lot more snappier". I suspect some of the extensions would benefit from indexes on the fields they add, but that's another and more specialized matter. I also suspect that matters such as the database type used (Sqlite, MySQL, ...), the type of table (MyISAM, InnoDB ... see http://www.unixcities.com/mysql/manual_table_types.html) will also affect the efficacy of indexes. As with so many things in the modern world, there are going to be a lot of "ifs and but" and and as I keep saying ... Context is Everything But yes, these are, in retrospect, so "obvious". Can we merge them into the core? -- Sometimes I lie awake at night, and I ask, "Where have I gone wrong?" Then a voice says to me, "This is going to take more than one night." Charles M. Schulz, Charlie Brown in "Peanuts" ___ Radiant mailing list Post: Radiant@radiantcms.org Search: http://radiantcms.org/mailing-list/search/ Site: http://lists.radiantcms.org/mailman/listinfo/radiant
Re: [Radiant] DB Indexes
I've applied Yevgeny's indexes (without the uniqueness constraints - YAGNI) and here's some circumstantial (i.e. not rigorous) results from running the spec suite: BEFORE Integration: 4.263972 Models: 51.04876 Controllers: 25.428781 Helpers: 1.062501 Lib: 2.166438 Generators:8.256952 --- real 137.84 user 58.80 sys 9.15 AFTER [speedup] Integration: 3.677267 (13.7%) Models: 38.548814 (24.5%) Controllers: 7.805954 (69.3%) Helpers: 0.987228 ( 7.1%) Lib: 1.283434 (40.7%) Generators:1.746686 (78.8%) --- real 87.59 user 60.00 sys 8.78 Now, you can throw out the differences on the Generators (they don't use the DB), Helpers, and probably Lib, but the changes on Integration, Models and Controllers are the most significant ones (and probably most accurate, since they take the longest). Plus, the overall 50 sec change in clock time to complete the specs was nice -- note that the user and sys didn't change much. Thanks, Yevgeny! Sean Yevgeny Smirnov wrote: I think, it's good to create the following DB indexes: add_index "pages", ["class_name"] add_index "pages", ["parent_id"] add_index "pages", ["slug", "parent_id"], :unique => true add_index "pages", ["virtual", "status_id"] add_index "page_parts", ["page_id", "name"], :unique => true Yevgeny Smirnov ___ Radiant mailing list Post: Radiant@radiantcms.org Search: http://radiantcms.org/mailing-list/search/ Site: http://lists.radiantcms.org/mailman/listinfo/radiant ___ Radiant mailing list Post: Radiant@radiantcms.org Search: http://radiantcms.org/mailing-list/search/ Site: http://lists.radiantcms.org/mailman/listinfo/radiant
Re: [Radiant] DB Indexes
Sean Cribbs said the following on 12/01/2008 07:34 PM: > I agree on most of those, however, I disagree about using unique > indexes. Uniqueness is already specified in the application layer and > unique indexes (in my experience) create more problems than they solve. It depends on how you look at it. I see them as integrity constraints. -- To announce that there must be no criticism of the president, or that we are to stand by the president, right or wrong, is not only unpatriotic and servile, but is morally treasonable to the American public." -- Theodore Roosevelt, speaking on President Wilson's crackdown on dissent after the U.S. entered W.W.I ___ Radiant mailing list Post: Radiant@radiantcms.org Search: http://radiantcms.org/mailing-list/search/ Site: http://lists.radiantcms.org/mailman/listinfo/radiant
Re: [Radiant] DB Indexes
Yevgeny Smirnov said the following on 12/01/2008 04:25 PM: > I think, it's good to create the following DB indexes: > > add_index "pages", ["class_name"] > add_index "pages", ["parent_id"] > add_index "pages", ["slug", "parent_id"], :unique => true > add_index "pages", ["virtual", "status_id"] > > add_index "page_parts", ["page_id", "name"], :unique => true Yes, that DOES make a difference in performance! My designs tend to make a lot of use of page-parts (and inheritance) (_parts_) of the sidebar, sections of a page with "go to top" or indexes. So the page_part and parent_id bits are very effective. (I never did figure out the 'virtual' stuff). I suppose another set of indexes would be to the "order by' for the Thanks! -- You will do me the justice to remember that I have always supported the right of every man to his opinion, however different that opinion might be to mine. He who denies to another this right makes a slave of himself to present opinion because he precludes himself the right of changing it. The most formidable weapon against errors of every kind is reason. I have never used any other, and I trust I never shall. --Thomas Paine (The Age of Reason) ___ Radiant mailing list Post: Radiant@radiantcms.org Search: http://radiantcms.org/mailing-list/search/ Site: http://lists.radiantcms.org/mailman/listinfo/radiant
Re: [Radiant] DB Indexes
I agree on most of those, however, I disagree about using unique indexes. Uniqueness is already specified in the application layer and unique indexes (in my experience) create more problems than they solve. Sean Yevgeny Smirnov wrote: I think, it's good to create the following DB indexes: add_index "pages", ["class_name"] add_index "pages", ["parent_id"] add_index "pages", ["slug", "parent_id"], :unique => true add_index "pages", ["virtual", "status_id"] add_index "page_parts", ["page_id", "name"], :unique => true Yevgeny Smirnov ___ Radiant mailing list Post: Radiant@radiantcms.org Search: http://radiantcms.org/mailing-list/search/ Site: http://lists.radiantcms.org/mailman/listinfo/radiant ___ Radiant mailing list Post: Radiant@radiantcms.org Search: http://radiantcms.org/mailing-list/search/ Site: http://lists.radiantcms.org/mailman/listinfo/radiant
[Radiant] DB Indexes
I think, it's good to create the following DB indexes: add_index "pages", ["class_name"] add_index "pages", ["parent_id"] add_index "pages", ["slug", "parent_id"], :unique => true add_index "pages", ["virtual", "status_id"] add_index "page_parts", ["page_id", "name"], :unique => true Yevgeny Smirnov ___ Radiant mailing list Post: Radiant@radiantcms.org Search: http://radiantcms.org/mailing-list/search/ Site: http://lists.radiantcms.org/mailman/listinfo/radiant
