Optimize validation of referenced NIDs from the URL

Which page are you getting these slow queries on: when viewing a node that has a Node Reference link on it or when creating/editing a node that contains a node reference?

The query you've referenced comes from Node Reference module itself, and as far as I can tell Node Reference URL doesn't ever call it directly (though it might happen if you're using the autocomplete fallback or when saving the node). But I can't find anything that would cause Node Reference URL Widget to cause this behavior. You should try using just a normal Node Reference widget (like Autocomplete) and see if the problem exists there also.

Something that may be more efficient in your case: Use a view to restrict the list of possible nodes instead of restricting by type. This will allow you to optimize your query by removing the ORDER BY and avoiding any sorts on the returned list.

Ah, okay I found where this is being called. Node Reference URL Widget validates that a NID is a valid reference before showing the widget on the node edit form. Here's the stack trace of sorts:

nodereference_url_widget()
optionwidgets_options()
content_allowed_values()
nodereference_allowed_values()
_nodereference_potential_references()
_nodereference_potential_references_standard()

It's worth noting the Node Reference itself will do this same validation upon saving the node, Node Reference URL Widget simply does it when the form is displayed before prepopulating the field, so you'd run into this on save with a normal widget, whereas you get it both on save and on the display of the form when using Node Reference URL Widget.

The only solution I can see in your case is using a View to optimize the query instead of letting Node Reference use its default one.

Heh, okay I know this is a lot of info all at once. I still think Node Reference is a part of the problem, but this patch might be able to help things further. Right now Node Reference URL Widget actually gets a full list of ALL referenceable nodes. This means in your case, it builds an array of 600,000 nid => title mappings, just to see if a NID exists in that list. I think this is because I wanted to avoid a call to a "private" function in Node Reference module, but if this gives us a significant boost, then it'll be worth maintaining.

Could you apply this patch and see if it helps with your problem?

Shoot, well that's what I get for putting up a patch and not trying it myself. This patch should work, but your report on if this fixes the performance problem would still be invaluable.

FWIW, if I comment out your NULL check it works!

After some more research, I found that Node Reference does NOT confirm that a reference is valid upon submission unless you're using the Autocomplete widget. This means that Node Reference URL Widget must do its own confirmation somewhere, so it's rather important that we keep this validation, otherwise you'll be able to reference any node just by changing the URL.

S U C C E S S - Very nice.....

Hurray! :-)

Excellent, I'll do a bit of validation testing and I'll probably put this as-is into the next release.

I checked it out and everything seems to be functioning exactly like before, just in a lot more efficient manner. Committed and I'll roll the 1.6 version shortly.