Hooks node_type, taxonomy and user knocks out our database server

We're certainly open to optimizing them - but I'm wondering if that syntax is MySQL only?

Also, how many nodes do you have - I imagine it must be very large?

Ah would love to see a patch (drupal.org/patch) and would love to see the EXPLAIN for before and after.

The debate for and against subqueries/JOINS continues on!

it doesn't look like postgres supports this syntax

Looks like Postgres can use a similar but incompatible syntax like:

UPDATE {apachesolr_search_node} sn SET changed = %d FROM {node} n WHERE sn.nid=n.nid AND n.uid = %d

http://www.postgresql.org/docs/8.0/static/sql-update.html

Discussed this with David Strauss - MySQL might handle this better in MySQL 6, but likely to support this better in the module we'll need to switch on the DB type. I have no idea how that will work in D7.

Scott Reynolds, is there a way to do an EXPLAIN on an update query with subquery?

Nope, apparently I hadn't had my coffee yet before posting that comment :-D

Looks like Postgres can use a similar but incompatible syntax like:

Incompatible how? Does it accomplish exactly what the original query was doing? I believe MySQL supports that syntax as well. http://dev.mysql.com/doc/refman/5.0/en/update.html

Mysql orders the table references differently and does not use the "FROM" keyword. I tried it before posting the above. MySQL can do something like:

UPDATE {apachesolr_search_node} sn, {node} n SET changed = %d WHERE sn.nid=n.nid AND n.uid = %d

compared to Postgres, where the table being updated is the only one before the SET:

UPDATE {apachesolr_search_node} sn SET changed = %d FROM {node} n WHERE sn.nid=n.nid AND n.uid = %d

Looks like we need to switch on DB type if we want to support this - anyone want to roll a patch and test it?

David Straus mentions that MySQL 6 will handle this bettter, but that's a ways off: http://www.scribd.com/doc/2546837/New-Subquery-Optimizations-In-MySQL-6

let's get this fixed before the next release

One possible improvement is changing OR to UNION.

From:

UPDATE {apachesolr_search_node} SET changed = %d WHERE nid IN (SELECT nid FROM {node} WHERE type = '%s' OR type = '%s')

To:

UPDATE {apachesolr_search_node} SET changed = %d WHERE nid IN (SELECT nid FROM {node} WHERE type = '%s' UNION SELECT nid FROM {node} WHERE type = '%s')

I think this is PgSQL compatible.

I think the problem is the IN query, not the OR

untested patch

Seems like that last query deletes from the {node} table too!!!

ok, this form seems to behave as expected for the delete.

Quick test to all 3 types of changes - looks ok. Committing to 5.x-2.x, 6.x-1.x, 6.x-2.x.

Needs to be ported to HEAD.

@justinrandell - I agree with your points, but in discussion with Crell it seems rather unfortunate that there is no way around sending such a poorly performing query to mysql in the context of DBTNG.

related core issue opened by Crell: #961604: dbtng drivers are not able to have their own condition classes

Marking #924214: Calling drush solr-reindex errors with unknown table in MULTI DELETE duplicate. It mentions a bug for some versions of MySQL:

In Mysql version: 5.0.47 the query:

db_query("DELETE FROM {apachesolr_search_node} USING {apachesolr_search_node} asn INNER JOIN {node} n ON asn.nid = n.nid WHERE n.type = '%s'", $type);

Causes the error:

WD php: Unknown table 'apachesolr_search_node' in MULTI DELETE

The issue is that using the alias for apachesolr_search_node means that mysql can no longer recognise the table to delete.

Still waiting for the core fix.

A solution until the DBTNG has resolved this could be :

if(db_driver() === 'mysql') {

}
else {
  db_update('apachesolr_search_node')
        ->condition('nid', $nid, 'IN')
        ->fields(array('changed' => REQUEST_TIME))
        ->execute();
}

Still have to investigate how the exact query would be for mysql and for other types (needs a lot of testing)

A patch that already separates the db_driver. Still a todo would be to write another query for mysql or for another db type.

Also, it seems that we are doing the same query 3 times. We could move this to

apachesolr_mark_node($nid);

This function could possibly retrieve one or more nids?

In 7 mysql and mysql are not separate.

Very interesting post about this behavior
http://stackoverflow.com/questions/3417074/why-would-an-in-condition-be-...

i first tried to make it happen in the dbtng layer but I bumped into a problem where the UPDATE does not support joins
(Info about that here : http://api.drupal.org/api/drupal/includes--database--database.inc/functi...)

// UPDATE {apachesolr_search_node} asn
// INNER JOIN {node} n ON asn.nid = n.nid
// SET changed = %d
// WHERE (n.type = '%s' OR n.type = '%s')
$types = db_or();
$types
  ->condition('n.type', $info->type)
  ->condition('n.type', $info->old_type);
$update = db_update('apachesolr_search_node')
  ->join('node', 'n', 'apachesolr_search_node.nid = n.nid')
  ->fields(array('changed' => REQUEST_TIME))
  ->condition($ored);

I think the only solution will be the db_query for mysql only

switch (db_driver()) {
  case 'mysql' :
    $query = "UPDATE {apachesolr_search_node} asn
      INNER JOIN {node} n ON asn.nid = n.nid SET asn.changed = :changed
      WHERE (n.type = :type OR n.type = :old_type)";
    $result = db_query($query, array(':changed' => REQUEST_TIME,
      ':type' => $info->type,
      ':old_type' => $info->old_type,
    ));
    break;
  default :
    $nids = db_select('node')->fields('node', array('nid'))->where("type = :new OR type = :old", array(':new' => $info->type, ':old' => $info->old_type));
    $update = db_update('apachesolr_search_node')
      ->condition('nid', $nids, 'IN')
      ->fields(array('changed' => REQUEST_TIME))
      ->execute();
    }

And the accompanying patch

Note: I am aware of a whitespace issue in the patch, but let's look at the concept and resolution first

Updated some documentation so future updates become easier

Yes, from my prior discussion with Crell, it probably just has to be db_query()

need to also fix this function:

function apachesolr_rebuild_index_table($type = NULL) {

There are more of them :

function apachesolr_nodeapi_mass_update($nodes) {
...
if ($published_ids) {
  db_update('apachesolr_search_node')->fields(array('changed' => REQUEST_TIME, 'status' => 1))->condition('nid', array_keys($published_ids), 'IN')->execute();
}
if ($unpublished_ids) {
  db_update('apachesolr_search_node')->fields(array('changed' => REQUEST_TIME, 'status' => 0))->condition('nid', array_keys($unpublished_ids), 'IN')->execute();
}

function apachesolr_nodeapi_mass_delete($nodes) {
...
  db_delete('apachesolr_search_node')->condition('nid', $nids, 'IN')->execute();

function apachesolr_search_type_boost_form_submit($form, &$form_state) {
...
db_update('apachesolr_search_node')->fields(array('changed' => REQUEST_TIME))->condition('nid', $nids, 'IN')->execute();

Mixing up patches, ignore this

A simple IN query is fine, these don't need to change - it's only the combination with a subquery

Untested patch but it does take the delete into account
will report back if I have tested all this functionality

Diffed on latest branch

I'm testing out all of the possibilities and somehow I get stuck/struck with the following :

  $query = "UPDATE {:table} asn
        INNER JOIN {taxonomy_index} ti ON asn.entity_id = ti.nid
        SET asn.changed = :changed
        WHERE ti.tid = :tid";
      $arguments = array(
        ':table' => $table,
        ':changed' => REQUEST_TIME,
        ':tid' => $term->tid,
      );
      $result = db_query($query, $arguments);

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''apachesolr_index_entities_node' asn INNER JOIN taxonomy_index ti ON asn' at line 1: UPDATE {:table} asn INNER JOIN {taxonomy_index} ti ON asn.entity_id = ti.nid SET asn.changed = :changed WHERE ti.tid = :tid; Array ( [:table] => apachesolr_index_entities_node [:changed] => 1325850555 [:tid] => 73

Translated to a real query this becomes
UPDATE apachesolr_index_entities_node asn INNER JOIN taxonomy_index ti ON asn.entity_id = ti.nid SET asn.changed = 1325850555 WHERE ti.tid = 73

When this query is executed straight in mysql it does not give me any problems... Weird..

Progress so far after review

With help from mhrabovcin, the following patch works but it does have a certain direct SQL injection problem. (that is, if entity_info can be altered in the cache)

Should we do some extra checking on the table variable?

An escaped version. I feel more comfortable using mysql_escape_string instead of nothing

probably want to use this
http://api.drupal.org/api/drupal/includes--database--database.inc/functi...

Adjusted

Needed some reroll

Wooaa, a 2 year old issue can be closed and marked as fixed! Committed :-)

Updated it in a follow up that apachesolr_index_get_indexer_table needed to become apachesolr_get_indexer_table

Reroll for 6.x-3.x