Do MySQL indexes only get built where there are reasonably large number of rows? Is it to be expected that when indexes are added during an upgrade to tables with small numbers of rows, that the indexes are fine even if their cardinality says none when 0 <= the number of rows <=20?

Background

I recently ran an upgrade and there were a small number of modules that had database upgrades, and two unhelpful error messages were provided:

* warning: array_merge() [function.array-merge]: Argument #2 is not an array in /home/mysite/htdocs/update.php on line 309.
* warning: Invalid argument supplied for foreach() in /home/mysite/htdocs/update.php on line 542.

Unfortunately, after running this once, none of the modules thought they still needed to be updated, so I started digging in to see what they claimed to have done.

the first is on line:
$_SESSION['update_results'][$module][$number] = array_merge($_SESSION['update_results'][$module][$number], $ret);
where $ret is previously set by:
$ret = module_invoke($module, 'update_'. $number);

The second error occurred on the last of these four lines:
foreach ($updates as $number => $queries) {
$output .= '

Update #'. $number .'

';
$output .= '

    ';
    foreach ($queries as $query) {

    The upgrade to content added indexes on nid to all of the content_field and content_type tables (missing one, however). Oddly, only where the number of rows was in the hundreds did the new indexes have a cardinality. When it was < 20 or so, it always said none. I tried doing a select query order by nid, the results came back correct but the index still had a cardinality of none. When I tried rebuilding the index by deleting and recreating it, it still had cardinality of none on these tables.

    Am I right in thinking these indexes will get built by MySQL when it thinks they will help improve perfomance, and that it is fine for their cardinality to be 'none' after the upgrade?

Comments

ainigma32’s picture

Could you post some additional info:

  • From what version to what version where you updating/upgrading Drupal?
  • What were the modules that needed db updates?
  • What version MySQL do you use?
  • How did you determine the cardinality (did you do a SHOW INDEX ON table) ?

AFAIK MySQL will create an index on a table even if there is only one row in it. But the index will not be used because the optimizer will prefer a full table scan as it will be more efficient in such a case.

- Arie

schildi’s picture

Got the same error message when trying to upgrade form imagecache 5.x-1.7 to 5.x-2.2 (Drupal 5.12).
Three messages appeared (the first for not unique column name action in table imagecache_action is due to a previous run):

Doppelter Spaltenname: &#039;action&#039; query: update_sql ALTER TABLE imagecache_action ADD COLUMN action varchar(255) NOT NULL DEFAULT &#039;&#039; in /html/drupal/includes/database.mysql.inc on line 174.
array_merge() [<a href='function.array-merge'>function.array-merge</a>]: Argument #2 is not an array in /html/drupal/update.php on line 309.
Invalid argument supplied for foreach() in /html/drupal/update.php on line 542.

After that no page using imagecache can be viewed any more.