[drupal-docs] Re: Database analysis of CivicSpace/Drupal

[Dan Robinson]

what problem are we trying to solve here?

Dan

> There are currently 107 database tables in the latest release of 
> CivicSpace. I believe there are 55 in Drupal core.  One of the most 
> important things you can do in improving database performance is to 
> make sure you have the right indicies for your database. Currently we 
> do not use foriegn keys to enforce relationships between database 
> tables.  I did an analysis of the CivicSpace code base and found that 
> there was 156 SQL statements with at least one JOIN. We can consider 
> these as application level relationships between tables. Here is a 
> visual representation of those JOINs: 
> http://civicspacelabs.org/home/files/schema.png I also analysed the 
> indicies of the current database schema. There are 182 indicies.
>
> The question was do they match? The answer was no.
>
> There are at least 68 fields that are joined which do not have 
> indicies from my initial analysis. Here are the fields in my blog post:
> http://civicspacelabs.org/home/node/12245
>
> The scientific method teaches us that results are useless, unless you 
> have a repeatable method.  I am going to explain in this book( 
> http://civicspacelabs.org/home/node/12246 ), Drupal-Docs folks, how I 
> conducted this analysis and it needs to be repeated and verified 
> before being acted upon. I built several tools with help from Drumm, 
> Ankur, Tom Eliaz, and Chris Johnson(cxj). It's possible those tools 
> ran amok.
>
> This is the first step in what I hope will be a continued analysis of 
> the database layer in Drupal. The next step will be to extend 
> devel.module to do batch updates of page executions times and mysql 
> query times. I have several theories about how to do this properly and 
> would appreciate your input. The final stage will be to create a 
> documented database schema that allows for external application 
> integration with common labels. If we want to integrate with 
> non-drupal applications we will have to allow for semantic mapping of 
> our database schema to a common database bus architecture(See Ralph 
> Kimball http://www.rkimball.com/).  But I'lll leave that for another 
> discussion.
>
> Cheers,
> Kieran
>
> P.S. I am off to go mountain biking, hiking, and kayaking in Hawaii 
> for a week so don't expect too many responses from me.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: civicspace-dev-unsubscribe at civicspacelabs.org
> For additional commands, e-mail: civicspace-dev-help at civicspacelabs.org
>
>