I'm assuming it's too late, but if not here's an easy performance win for D6:
The type column of the {system} table is currently a varchar(255) field. Yet in 99% of all Drupal installations it stores either the text 'module' or the text 'theme' and nothing else. This is already a strong case for making it an int column and defining constants for module and theme. Then, on every page, there is this query:
SELECT filename FROM system WHERE name = 'user' AND type = 'module';This query always takes over 1 ms to run making it not a criminally slow query, but relatively slow, nonetheless. This is because the list of indexes available doesn't help the query much
By shortening the {system}.type column to 32 chars and adding an index we reduce the query by around 60%... and this query runs on every page load.
Comments
Where else
Is there any other location in core that checks the type of the {system} table?
--
http://ken.therickards.com/
http://savannahnow.com/user/2
http://blufftontoday.com/user/3
--
http://ken.therickards.com/
char(32) wouldn't work on MySQL
In MySQL using fixed width columns only makes sense if all columns are fixed width. Changing the type column would be very smart from a normalisation (and performance) POV though.
If you're looking for quick performance increases then changing the search system would probably be much more effective on most Drupal installs. search_index contains a varchar called 'type' that contains the string "node" for most of the rows, this could very easily be changed into a simple INT (or even smaller). A little bit more work would be normalizing the 'word' column by creating a search_words table all unique words in the DB and an INT that identifies that word. Then replace the entries in search_index.word by the relevant INT. search_index gets huge if your site contains a fair amount of content, it really can't scale beyond a handful of large articles (except if you throw a large amount of hardware (esp memory) at it). Making these simple changes would make search_index quite a bit smaller.
For Drupal7 it would be nice if you could use something like Sphinx or Lucene as a search backend. SQL just isn't suited for doing fulltext search.
Scheduled for removal...
I can't remember where but I think there is an open issue to ditch the system table entirely and replace it with tables modules, themes, etc. (I'm not actually sure there is an 'etc'). If not, there should be. This is better than normalizing the type column because it also eliminates the join and there is no particularly good reason modules and themes need to be listed together.
the only "etc." I know of
the only "etc." I know of would be theme_engine