Querying content type by name is inefficient


Almost all content queries in Orchard have a join on the content type table that is queried by name, using an IN operator. When there is only one type, it would be considerably more efficient to use = rather than IN, but a first and very effective mitigation of this problem is to create an index on the content type name.
I was able to bring the time for one specific particularly affected query from 7s down to 10ms.


AimOrchard wrote Dec 12, 2012 at 10:02 AM

Well in general Orchard needs more indexes 'by default'.

As now we're all doing double work figuring out what indexes are required for larger websites.

sebastienros wrote Apr 30, 2013 at 12:16 AM

Fixed in changeset 996515f8207e

sebastienros wrote Apr 30, 2013 at 12:17 AM

We need to work on another filed issue for the indexes. Maybe we could provide a feature to enable default indexes on core features. @AimOrchard, you might be able to help us.

AimOrchard wrote Apr 30, 2013 at 7:03 AM

Well I never really 'thought' about much of the indexes, I kinda went for what sql server management studio suggested + I used one of the 'detect missing indexes' sql scripts that you can find on the internet to decide what indexes to add.

sfmskywalker wrote Mar 28 at 1:28 AM

Fixed in changeset 4680c32825f20a83144864f3022ffd9ee03513ec