MySQL support and table indexes?

Jan 22, 2011 at 8:17 PM

Hi Guys,

I have now completed support for MySQL in my own code base for Orchard. Because it has to be integrated into the lowest level of the Orchard Core, it is not possible to present the support as a normal module. I would assume there would be others interested in MySQL support also, so would this be something that would be interesting to submit as a patch for Orchard? If so, how would I got about doing that?

The code itself consists of a new data provider, along with patches to the setup code to enable support for MySQL during the setup process. If I made a patch, it would probably need some documentation to go along with it to help other users get past the oddities of using MySQL on (special Medium Trust clone if you need Medium Trust and Web.config updates to indicate the correct MySQL assembly to load).

Anyway, one of the things I noticed once I created the database with MySQL is that NHibernate created all the tables and everything works, but there does not appear to be any indexes on any of the tables except for the primary key indexes. Is that normal? When a site gets big, does NHibernate automatically add indexes to the database where necessary, once it identifies where they should be used? Or is that something the DBA still needs to manage with NHibernate?

Jan 24, 2011 at 5:57 PM


First let me say that it's great that you did that: anything that can improve the reach of Orchard is good. Now the little problem that I have is that we would like this to be possible through a module rather than through a patch for each database engine. Your work will certainly help us identify what parts of the setup we need to open up for this to happen, and if you don't mind I'd like to ping you when we do that in order to verify that you can refactor your work into a module. Does that sound like an acceptable plan?



Jan 24, 2011 at 7:10 PM

Sure thing. I actually think it would be pretty easy to do to be honest. You would just need a way to move the DB providers into separate modules, and out of the core itself. Then you could expand on the services provided by the DB provider model to provide the stuff needed by the setup code to list the database as an option during the setup process, if the module is installed. It would need to be dynamic during the setup process I suppose, as it would need to dynamically try to find and load the database modules during the setup process, because the rest of the module system won't be alive yet. Although the must be something already in Orchard that enumerates available modules, since once modules are installed they show up in the list. It would be pretty easy to build modules to support all the databases that NHibernate supports, which would be really nice.

I am not familiar enough yet with the module system, but I would be happy to help when you get to that point. I have all our code as a branch in Perforce which is what we use for our SCM, and it is easy for me to generate patches to submit for files I have changed. Or I could work out how to use your SCM system and submit the changes when you are ready :)

I am still curious how indexing of tables works with NHibernate. Is there no need with an ORM to have anything other than the primary keys in the tables indexed, or does NHibernate add indexes when it decides they are necessary dynamically? I am honestly not familiar at all with NHibernate, and only have a surface knowledge of the Entity Framework. So these thing are quite new to me :)

Jan 25, 2011 at 11:37 AM

It seems like indexes aren't dynamically added by NHibernate.

Even better, the whole SchemaBuilder seems to be Orchard specific, not using NHibernate to create and alter tables.
Search for "alter table {0} add index {1}" and u'll find it in DefaultDataMigrationInterpreter.
This class is doing all schema stuff.

An Index would be added by using

SchemaBuilder.AlterTable("MyTable", table => table
      .CreateIndex("MyIndex", "column1", "column2")

Jan 26, 2011 at 4:53 PM

Are any indexes currently being added anywhere in the code? Or is that something that probably needs to be looked at in the future, to ensure that performance is good once an Orchard site starts to get a lot of content?

One of the biggest downsides I have found when trying to use something like EF is the fact that you don't get to see the SQL that is generated easily (have to work to see it). Hence it is difficult to determine what you should index. Perhaps there are tools available for EF and NHibernate to help determine when you should index columns for performance?

Jan 26, 2011 at 4:55 PM

Yes there are tools and we're using them. Is there a specific performance problem that you're seeing?

Jan 26, 2011 at 5:54 PM

No, not at this time. Just that when I looked at all the tables generated in MySQL, I did not see any indexes. But perhaps I was looking in the wrong place? If you can let me know an item that should be indexed, I will double check the MySQL tables to make sure the indexes are showing up.

Jan 26, 2011 at 6:01 PM

I can check, but I don't think we have any indices other than the keys.

Jan 26, 2011 at 6:14 PM

Ok, so at some point we will need to consider indexing stuff other than the primary keys for a site with lots of content? It all depends on whether there are any complex joins generated by the NHibernate code that joins to foreign keys in other tables that would need to be indexed, or if filters are used to restrict a result set on a column that is not indexed. The hard part with an ORM is figuring out what that would be :). It is easy when you write your own SQL, as you can just look at the SQL in a SQL explorer and do an explain on it.

I suppose if there was a way to see the SQL on a page somehow if you find something slow, you could look at the SQL and then determine what to index on.

Jan 26, 2011 at 6:27 PM

You can use a profiler such as NHProf for that but adding indices just because is sometimes counter-productive.

Jan 26, 2011 at 7:33 PM

I would agree entirely. Adding them where it makes sense is most important, and for that, you need a large data set to test with :)

Feb 6, 2011 at 5:05 AM

@kendallb: Exactly the stuff I was looking for. I have seen plenty of other folks looking for MySQL support as well(a quick web search would reveal). Would you mind sharing your code / findings?

Thanks in advance :)

Feb 7, 2011 at 5:01 AM

Sure thing. Maybe I will make a ZIP file with the file changes needed (and pre-built DLL's). I am on the road at the moment but I could probably get to it in the next few days.

Where would I post the ZIP file?

Feb 7, 2011 at 5:39 AM


I am not sure if codeplex have an option of sharing files other than hosting them as a project here. the easiest i can think of is hosting the zip file on a free file sharing sites like,

and share the link here. Let me know if that would work...


Thanks Again!

Feb 7, 2011 at 9:02 AM

Well until we can find a better place for it, I put the zip file on our web site here:

Make sure you have deleted all the sites from your Orchard setup so that it will go through the setup process again, and MySQL will be listed as one of the options. Make sure you create the database you want to use first and give the permissions to it that you want for the user you plan to use for your site, and then just enter a valid MySQL connection string into the edit box for the connection string.

Let me know if it works for you!

Feb 8, 2011 at 2:27 AM

WooHoooo!!!! It did work... could see the whole bunch of tables created and the home page and dashboard appeared perfect. cant thank you enough :) 

I haven't completed verifying everything is working as expected... at least creating new pages didnt seem to work for me so far... Might need to look more closely.


Thanks a ton :)


Feb 8, 2011 at 3:24 AM

Excellent. Let me know if you run into any issues, but I suspect everything is good. The only thing I think we need to do in the future is possibly add some indexed to the tables to account for larger web sites, but until our site has a lot of content I don't have anything to benchmark yet :).

Feb 8, 2011 at 10:25 AM
Are either of you guys going to publish a module ?

Feb 8, 2011 at 3:04 PM

Right now, it is just a patch so there is still quite a bit of work to wrap this into a module most likely.

Feb 8, 2011 at 7:53 PM

It is not possible to make this into a module since you need to modify some of the core components for Orchard to add the database support. I think Bertrand said earlier that the plan is to build a module system to support other databases, so at some point this will happen, but until then you have to patch the core code and recompile it. I will be happy to help out building a module system for this if such a project gets underway.

Apr 27, 2011 at 5:50 PM

Please refer to: