While this is not a direct Drupal core issue, it's a usability issue as far as modules.

And before someone waves their magic wand and says modules don't matter - to many users, they do. In fact, they add a lot to Drupal.

I sincerely do not understand why node_revisions and node are separate tables. When a module has it's own table and has to deal with a node, you may well be looking at a nested inner join to do that - which may work well if you have your own server, but many users do not.

Comments

killes@www.drop.org’s picture

killes@www.drop.org commented
Version: 4.7.0 » x.y.z

features go into the next version.

I don't quite understand why having two tables would be a problem for modules and what having your own server has to do with it.

Taran’s picture

Taran commented

Look into the performance hit of 2 or more INNER JOINs. There's a distinct hit which, with the quotes module as an example, causes CPU warnings in shared hosting scenarios. Why? Because it works like this:

Check First Table{
     Check Second Table{
          Check Third Table {
     }
}

With a site of over 10,000 nodes, you can expect problems because INNER JOINs are really nested loops in MySQL.

And I still don't have a reason why node and node_revisions are separate. I know why I believe that they shouldn't be, but this design decision must have some reasoning behind it which I cannot fathom. There must be a reason.

killes@www.drop.org’s picture

killes@www.drop.org commented
Status: Active » Closed (won't fix)

the reasoning behind splitting them up was that often you need to join against the node table. However, often you only require the author's ID ot the title and not the full body. So in fact this changes a lot of queries to be lighter on the mysql server. There are some benchmarks here:

http://drupal.org/node/7582

Marking this "won't fix"

Taran’s picture

Taran commented

OK, so you won't fix it - understandable.

This means module developers need to be aware of the INNER JOIN issues as related to the two tables. The obvious workaround (at this time, anyway) for the quotes module and perhaps the flexiinode moduel in the future is to become parts of either table node or node_revision.

Your thoughts? I'm about to try this with the quotes module first, before I even look into flexinode... This is the best solution I can think of, but before I run off hacking away, I'm taking some time to gain input from the more experienced Drupal hackers.

killes@www.drop.org’s picture

killes@www.drop.org commented

The first two queries in quotes.module that do a join on the node_revisions table don't need it at all since it does a node_load just below. However, I would probably rather keep the join and remove the node_load.

All things considered, I don't see why doing this query and then loading the nodes (which also does the query) should be a problem for your mysql server. The front page on /node does the same.
You may want to reduce the number of quotes shown on that page.

Taran’s picture

Taran commented

It's only showing 10 quotes on that page... but... I'll look into what you're saying. Thanks. Trying not to shoot myself in the foot.

Taran’s picture

Taran commented

OK, looked at it deeper (also tweaked the quotes module here). I removed one of the INNER JOINs in those queries.

I opted for that because through experimentation, I found that the combination of those INNER JOINs and the node_load was sending the server into danger territory. The reason node_load remains in use is because of the piggyback in node_load which allows the quotes to display as proper nodes in a list, with clickable titles and the author embedded. Simply doing a node_view on the results of the query causes incomplete nodes being listed (no author, no link to actual node). The piggyback functionality is probably the key area here... as you say, /node uses the same code *but* it doesn't use the piggyback functionality as iteratively unless every node on the front page uses piggyback functionality.

So - I was barking up the wrong tree, but it doesn't mean that there isn't a right tree. So now I'm hoping I'm at the right tree.