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

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?