[development] Views & lots-o-data

[Ryan Courtnage ☠]

Thanks a lot guys.  I'll examine the query and try an index.

Cheers

On 10/7/07, Larry Garfield <larry at garfieldtech.com> wrote:
> I find that I almost never use full node or teaser views, as they do a full
> node load on each view.  That's extremely expensive.  Instead, almost ever
> view I have is a List View, which is a single query.  It's much faster in
> most cases.
>
> The next thing to do is take the query Views generates and run an EXPLAIN on
> it.  Dollars to donuts you can get a huge performance boost by adding one or
> two carefully placed indexes in your database.
>
> Cheers.
>
> On Sunday 07 October 2007, Ryan Courtnage ☠ wrote:
> > 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;
> > }
>
>
> --
> Larry Garfield                  AIM: LOLG42
> larry at garfieldtech.com          ICQ: 6817012
>
> "If nature has made any one thing less susceptible than all others of
> exclusive property, it is the action of the thinking power called an idea,
> which an individual may exclusively possess as long as he keeps it to
> himself; but the moment it is divulged, it forces itself into the possession
> of every one, and the receiver cannot dispossess himself of it."  -- Thomas
> Jefferson
>