[development] Views & lots-o-data

[Ryan Courtnage ☠]

Just a follow-up.  I turned on mysql query logging to see which query
was holding me up.  Turned out to be a COUNT, which I assume is needed
to build the paging properly:

---snip---
SELECT count( DISTINCT(node.nid)) FROM node node  LEFT JOIN
node_charity node_charity ON node.nid = node_charity.nid WHERE
(node.type IN ('node_charity'))
---snip---

Creating an index on my table's "nid" column made all the difference
in the world.

Thanks again,
Ryan

On 10/7/07, Ryan Courtnage ☠ <ryan at courtnage.ca> wrote:
> 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
> >
>