Drupal: Many fields/content-types equal *many* tables and after a point make MySQL very slow

As I create more and more fields and content-types, I see that Drupal creates a huge numbers of tables (>1k) in MySQL and after a point my system becomes very slow.

I have trie开发者_JAVA百科d several MySQL performance tuning tips, but nothing has improved the performance significantly. Enabling caching makes for good speed in the front-end, but if I try to edit a content-type from the admin back-end, it takes for ever!

How do you cope with that? How do you scale Drupal?

If sheer number of tables has become the database performance bottleneck, I'd have to agree with Rimian. You can define your own content types programmatically, and then develop your own content type model by leveraging the Node API.

API documentation and an example of doing just that are here: http://api.drupal.org/api/drupal/developer--examples--node_example--node_example.module/6

The code flow is basically:

• Make Drupal recognize your content type
• Define the fields it needs to take using the Forms API
• Define how each of the Node API's functions should behave (view, load, save, etc.).

This allows you control over how things are stored, yet still gives you (and all contributed modules) ability to leverage the hook system for Node API calls.

Obvious drawbacks are missing out on all of the features/modules that directly depend on CCK for their functionality. But at >1k tables (which suggests a gargantuan number of content types and fields), it sounds like you're at that level of custom work already.

I worked on a Drupal 5 site with more than a million nodes and this was a serious issue.

If you're scaling Drupal up to enterprise level, consider not using CCK for your fields and developing your own content model with the node API. It's actually quite easy.

The devel module offers a Performance Monitoring tool that will show you all queries performed organized by time, showing which hooks and modules called them, etc.

Just don't run on production.