[development] CCK Field Indexing

[Aaron Winborn]

At DrupalCampNYC, a small group of us have been discussing a common performance issue we've experienced with Views/CCK, and have come up with a possible solution.

Often, Views will create difficult queries that sometimes require examining a slow query log and tweaking field column indexing. It might be good for developers to be able to easily tweak indexing before it gets to that place. For instance, a library might have a node type with book title, author, summary, isbn, and not want indexing on isbn (as a hypothetical).

When scaling, some indexes are unnecessary and grow exponentially. In other cases with obscure queries, there are not indexes where you really need them.

A possible solution, probably using a contrib module, would be to add a field set for 'advanced settings' or 'advanced indexing settings', with a big red warning for people, telling them not to touch it unless they absolutely know what they're doing. Inside would be a check-box or selector for primary key, key index, and no index. With indexing on by default.

Firstly, is there already something like this? Secondly, although we could certainly use this functionality, is there a preferred way to handle this? Thirdly, if we created this module, what's a good name? CCK Field Indexing? It would need to plugin at the very least with the core cck fields, maybe with .inc files for other types.

Related, I think that Views 2 should have an 'explain query' built in when building the view, maybe as part of the 'analyze' button, or as part of the query displayed, which would help tweak your queries well before it gets to production.

Thoughts?

Thanks,
Aaron Winborn