[development] Views & lots-o-data

[andy at spiders-lair.com]

Is your "city" field indexed?

CREATE INDEX {my_modules_table}_city ON {my_modules_table} (city);

regards,
--Andy

----- Original Message ----
From: Ryan Courtnage ☠ <ryan at courtnage.ca>
To: development at drupal.org
Sent: Sunday, October 7, 2007 9:27:23 PM
Subject: [development] Views & lots-o-data

Hi all,

I've just written a node module that implements a view.

First, I got my simple view to display the node title & body - this works.

Next, I add a field that I've exposed from my custom module's table (a
"city" field).  This is where I run into a problem.  My module's
node-type has over 88,000 entries.  The query that the Views module
runs will cause mysql to pin the CPU (I've watched it pin for over 100
minutes before killing the process).

What can I do to improve the perfomance (in a big big way)?  I'm not a
DBA, is there something I should be doing at the DB table level to
improve performance?  Or perhaps there's a Views option I'm missing?

My views implementation follows.  I've commented above the
$view->field array that causes the problem.

Thank you!
Ryan
irc: Bentley

----

function node_charity_views_tables() {
  $table = array(
    'name' => 'node_charity',
    'join' => array(
      'left' => array(
        'table' => 'node',
        'field' => 'nid'
      ),
      'right' => array(
        'field' => 'nid'
      ),
    ),
    'fields' => array(
      'address_city' => array(
        'name' => t('Charity: City'),
        'help' => t('City of Charity'),
        'sortable' => FALSE
      ),
    )
  );
  $tables["node_charity"] = $table;
  return $tables;
}

function node_charity_views_arguments() {

}

function node_charity_views_default_views() {
  $view = new stdClass();
  $view->name = 'Charities1';
  $view->description = 'CCOs';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'Charities';
  $view->page_header = '';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = '';
  $view->page_empty_format = '1';
  $view->page_type = 'list';
  $view->url = 'charities1';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '50';
  $view->sort = array (
  );
  $view->argument = array (
  );
  $view->field = array (
    array (
      'tablename' => 'node',
      'field' => 'title',
      'label' => '',
      'handler' => 'views_handler_field_nodelink',
      'options' => 'link',
    ),
    array (
      'tablename' => 'node',
      'field' => 'body',
      'label' => '',
      'handler' => 'views_handler_field_body',
    ),
    // the following array causes cpu to pin for hours.
    array (
      'tablename' => 'node_charity',
      'field' => 'address_city',
      'label' => '',
    ),
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
        0 => 'node_charity',
      ),
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(node, node_charity);
  $views[$view->name] = $view;

  return $views;
}