moved this topic to the developers forum…

cause is this where I say what???  lol…  I dont believe we have a database diagram – at least not one I am aware of…  Andy might…  okay, I give, what is that?

Sounds like we are quickly finding the area you can help us out initially…

Steve, just reading your response tells me that unless Andy has it somewhere, it probably doesnt exist.  

I was thinking something along the lines of this: http://codex.wordpress.org/Database_Description

With the idea of adding the ability to create plugins (if Im reading correctly) in v5, this would be a handy piece of documentation to add to the wiki for 3rd party development of plugins.

As I said, Im glad to help and Im glad to see that there is something that I can do outside of the limited knowledge that I currently have.  But Im reading like CRAZY right now!  Helps that I work with SQL on pretty much a daily basis and have probably dug deeper into SQL Server than I really care to admit.  

well we have a schema type file… which contains some of the same info, but no, not a formal diagram…

in your code, look in simple-forum/problem-with-post-edit-buttonelp/documentation/database-script.sql…

actually, its a generation script for raw db… might help though…

absolutely agree on the value of it with regard to plugins and themes…

I see the indexes being created now.  I was browsing through my database and saw some indexes, but couldnt understand where they came from.  This makes more sense now and appears as if there are indexes on the database.  

I feel like such a noob.  LOL

I know there are indexes… and overtime as we write different queries, we add indexes where it makes sense… but we certainly could be missing some too…

When I was looking at it, I was looking at the PHP code and didnt know about the database script that was output, so I didnt see the indexes right away.  Its also entirely possible that I overlooked the indexes completely, especially due to the differences in syntax between flavors of SQL.  

Either way, I think a database diagram with some good documentation similar to what WordPress has would be valuable.  I'll see if I can get something drawn up.  If nothing else, it would be a good exercise to help me understand the underlying structure.  

I'll have to look at svn a little bit and get an overview of how to use it, as I'd like to be looking at the 4.4.1 codebase as well as 5.0, since Im sure there are modifications to the database.

oh, yes… fairly large differences… and huge query rewrites…

we actually have a 4.4.2 in works, but no planned release date – few bug fixes, nothing major… so just stocking up on changes and will eventually release it or will do so if a major bug came up or some shocking new feature like huge query throughput increase 😉 occurred…

Steve has heard this rant before so he may want to skip this post but as Tim is new on the scene I can preach all over again!

To a certain extent we are constrained by both the WP database schema and the WP db class code ($wpdb) that performs all db interaction. We can do nothing about the first and do not really want to write our own db layer if we can help it – which I think is understandable.

The WP db schema is, to be honest and in my opinion, pretty poor. It goes back a long way and to be fair, mySQL wasn't quite the engine it has grown in to when the schema was first constructed. But they are more akin to flat files as they make no use at all of relational features and constraints. (The taxonomy tables are better and were a much later addition when more actual thought and design went into them).

The bad news – at least I see it as bad news – is that while $wpdb seems to be able to make use of Views, which it should as it just sees a View as a Table, it doesn't support Stored Procedures. At least if it can run them and get data returned it can't pass arguments which really renders them redundant. I had been hoping that with the recent move to make mySQL V5 a minimum requirement then $wpdb would get a makeover to allow for procs but there seems to have been no movement or actual discussion on this.

I don't know about you but I would quite like to be using procs for some of our bigger, complex, multi-table queries but short of constructing a new class that is off the cards. So we stick to more traditional methods.

As to indexing I have to say it is not one of my strong points and I would welcome a more knowledgeable pair of eyes taking a look at what we have now. Using phpMyAdmin, I kept a close watch on the stats for about a year noting where they reported tension and we added a few more indices on the strength of that. But we may not have it right or be making the most efficient use of indexing. So please do take a look and shout if you think we should make changes. Constructive criticism is totally welcome.

Oh yes… if and when you do get a better handle on our schema you WILL find some cases of rabid denormalisation! Sometimes you do just have to put performance over elegance!

Well normalization is all fine and well, but I absolutely agree that there is a trade off necessary for performance.  Thats what they dont teach you about!

I'll take a gander at the database and see what I can come up with.

I monitor the WP trac daily and have seen no indication to update $wpdb for MySQL 5…  they are making lots of updates for php 5 vs php 4 but as of yet, none for MySQL

I know I can learn here!

It may be worthwhile, in my opinion, to stick with whatever WordPress is doing, database wise, until they're proposing something.  Im not well versed in the differences between MySQL 4 and 5, although Im running my wordpress site on 5 right now with no issue.  But that doesnt mean there arent enhancements to MySQL 5 that we would want to take advantage of.  Certainly some things to look at.

WP is actually dropping support for php 4 and mysql 4… but doesnt mean they will start changing things yet to take advantage of mysql 5.

agree on using wp db as basis. we have been using mysql 5 here for years…

I spent some time looking at the database tables in MySQL Workbench, which is a pretty handy tool if you don't have a copy of it.  It basically lets me do 3 things: queries, diagrams, and server maintenance.  This is a replacement tool for what used to be known as MySQL Query Browser.  In my opinion, a tool like this is invaluable and a hell of a lot easier to use than phpMyAdmin, once you get used to the interface.

I created a basic diagram of the spf database tables only, so far.  Might be cool to show both sets of database tables (wordpress on the left and simple press on the right) at some point, since Simple:Press is a plugin to WordPress.  I dont know that having both sets of database tables is really necessary right now, which is why I haven't done anything with it. 

I did find some inconsistencies within some of the naming of fields and the types between like field names within different tables.  Overall, they're not critical, but probably should be cleaned up at some point (since one of my understandings of v5 is to clean up some stuff).  The diagram helps point some of those out, but here are a couple of examples.

sfgroups has a primary key of 'group_id'.  However, sflinks has a primary key of 'id'.  While this is not an issue from a database performance perspective, it does show an inconsistency, which, when designing plugins, could be confusing for other users to understand why there's a difference in the naming scheme.  As I said, not critical, just an inconsistency.

Another that I found was some minor data type mismatches. sfgroups has a primary key of group_id which is a bigint(20).  However, in sfdefpermissions, the group_id is mediumint(8).  The only issue I really see here is that if there are more groups than permissions (which is more likely, I think), then once the group_id becomes larger than what mediumint will support, it will result in data type mismatch errors which will need to be fixed down the road.  For now, probably not a serious issue, but just an inconsistency.

Both of the above mentioned things are just a fresh set of eyes trying to understand what's already in place.  Its pretty likely also that the id's probably dont need to be bigint, but going overboard on something like an ID is probably not a bad thing, since you almost never have to touch the thing.

I'll email you both the image and you can let me know what you think.  I guess a good question would be where would I be able to store things like images that I could link to, without having it stored on some server somewhere that doesnt belong to Simple:Press.  Seems to me something like this, we would want to store.  Once we're happy with it, Im sure we'd include it as part of the documentation.

will have to check out that other tool…

work up some recommendations and lets see what we got…