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 tried 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?
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.
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:
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.