I'm running a site with something like 10k users and 50k nodes. I am working on a view with a query time of something like 300ms. When I add the content profile relationship it works as expected, but my query time jumps up to something like 9000ms, obviously not acceptable for a front facing web page. Any thoughts as to the reason or a way to pull in content profile fields to my view without slowing things down to a standstill? Here's the offending query:

SELECT COUNT(*) FROM (SELECT node_data_field_article_image.field_article_image_fid AS node_data_field_article_image_field_article_image_fid, node_data_field_article_image.field_article_image_list AS node_data_field_article_image_field_article_image_list, node_data_field_article_image.field_article_image_data AS node_data_field_article_image_field_article_image_data, node.type AS node_type, node.nid AS nid, node.vid AS node_vid, node.title AS node_title, node.created AS node_created, node_revisions.teaser AS node_revisions_teaser, node_revisions.format AS node_revisions_format FROM drupal_node node INNER JOIN drupal_users users ON node.uid = users.uid LEFT JOIN drupal_node node_users ON users.uid = node_users.uid AND node_users.type = 'profile' LEFT JOIN drupal_content_type_article node_data_field_article_image ON node.vid = node_data_field_article_image.vid LEFT JOIN drupal_node_revisions node_revisions ON node.vid = node_revisions.vid WHERE node.type in ('article') ) count_alias

Comments

alb’s picture

alb CreditAttribution: alb commented

have you solved?
but this query that is 'only' a count is really necessary?
with devel I noticed that in a view there are some of this type of queries; but are really necessary?

for your sites which type of server (or vps or host) you need?
cpu, ram, band etc
and which contemporary users you have?

djdevin’s picture

djdevin
Location Philadelphia
CreditAttribution: djdevin commented

Old issue but found a solution.

The "count" query is necessary, Views uses it for the pager.

I had to manually add an index to the node table, with 15k+ joined profiles, MySQL will lag.

This clause was slowing it down:

LEFT JOIN drupal_node node_users_ethosce_course_report ON users_ethosce_course_report.uid = node_users_ethosce_course_report.uid AND node_users_ethosce_course_report.type = 'profile'

So I added an index to the {node} table:

  $ret = array();
  db_add_index($ret, 'node', 'uid_type', array('uid', 'type'));
  return $ret;

D7 views does it totally differently so probably doesn't apply there (for Profile2).