Optimizing a Views generated block

elfur@drupal.org's picture

I am struggling with the speed on my Views generated blocks. These blocks are essential for the functionality of the front page.

Logged out, with cache enabled both for page and block devel returns horrid page load times:
Executed 144 queries in 59187.65 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 59675.94 ms.

Logged in as admin (uid=1) shows equally horrid page load times:
Executed 182 queries in 59032.05 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 59650.23 ms.

devel module also shows me that the reason is those manic queries that are called for the views blocks. I enabled access to devel result to anonymous so you can see for yourself the horrid results: http://krepputal.elfur.org. (what's up with the seperate count query for the more link?).

I started by posting this post over at drupal.org, but realise that this group is propably the right place for solving the actual problem I have.

I'm building a site that handles two kinds of data:
1) feeds aggregated into nodes via FeedAPI and related modules.
2) nodes created by users via the forum module.

I'm worried that if this is the page load time during development, how will it be when more users will visit?
I've enabled colophon to display the modules used, but apparantly it isn't displaying any modules apart from the core in the module list (see http://krepputal.elfur.org/colophon)

I build the front page using the views module, generating blocks of categorised data for display. As this is feed aggregation and the number of distinct feeds aggregated is in the second hundred (it shows on the colophon page, casual count says 117) the number of nodes in the node table is rising fast.

As I said before, I've enabled cache in all places I can think of. I've even downloaded the cacherouter module and am trying to get that to work (why that doesn't work is another issue for another thread).

I'm basically at my wits end, and the frontpage display is the key to the site, so not displaying all that data is really not an option.

Any suggestions?

Comments

Hi performance tuning is not

bennos's picture

Hi
performance tuning is not simple and depends on your site structure and user activity.
Try some cache modules like cacherouter, memcache or boost, but your first look should be a good setup for the database server and the webserver.
have a look in this group. You will find some good setups for webservers and database.

Which Queries Are Long?

Jamie Holly's picture

Can you paste which queries are taking the longest time? One thing to remember with views is that it doesn't create indexes, so you could be in a situation where you got some queries resorting to nasty stuff like filesort - which kills performance.


HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.

the queries are visible at

elfur@drupal.org's picture

the queries are visible at the bottom of http://krepputal.elfur.org. The views queries come in pairs, because of the "more" link, this is an example of the query types:

7589.78   1   execute SELECT COUNT(*) FROM (SELECT node.nid AS nid FROM node node INNER JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN feedapi_node_item_feed feedapi_node_item_feed ON node.nid = feedapi_node_item_feed.feed_item_nid LEFT JOIN feedapi_node_item feedapi_node_item ON feedapi_node_item_feed.feed_item_nid = feedapi_node_item.nid LEFT JOIN feedapi feedapi ON feedapi_node_item_feed.feed_nid = feedapi.nid LEFT JOIN node feed_node ON feedapi_node_item_feed.feed_nid = feed_node.nid WHERE (term_node.tid = 27) AND (node.type in ('feednode')) ) count_alias

7545.09  1   execute SELECT node.nid AS nid, node.title AS item_title, feedapi_node_item.url AS original_item_url, feed_node.title AS feed_title, feed_node.nid AS feed_nid, feedapi_node_item.timestamp AS feedapi_node_item_timestamp FROM node node INNER JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN feedapi_node_item_feed feedapi_node_item_feed ON node.nid = feedapi_node_item_feed.feed_item_nid LEFT JOIN feedapi_node_item feedapi_node_item ON feedapi_node_item_feed.feed_item_nid = feedapi_node_item.nid LEFT JOIN feedapi feedapi ON feedapi_node_item_feed.feed_nid = feedapi.nid LEFT JOIN node feed_node ON feedapi_node_item_feed.feed_nid = feed_node.nid WHERE (term_node.tid = 27) AND (node.type in ('feednode')) ORDER BY feedapi_node_item_timestamp DESC LIMIT 0, 30

the first of the two, is counting the rows in the table, and if I remove the "more" link, it disappears regardless of whether I have a pager visible or not.
The second of the two is basically selecting data for five items: feednode title, parent feed title, node url, feed item url, and timestamp.

--
adding extra info: I have 14 views on the front page, but only 13 are giving me griveances - the query that uses page display rather than block display appears to be doing fine (the query filtering on taxonomy tid = 142, returning in 0.13ms).

Try adding an index on your

Jamie Holly's picture

Try adding an index on your node table containing both type and created. Indexes can get tricky with joins, and there are a lot of them in there.


HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.

Views takes a safe position

moshe weitzman's picture

Views takes a safe position and does not cache its blocks by default. It has code that is commented out which would enable admin to choose a cache mode for each block (search for 'block_caching'). You probably will be fine if you just change the value of 'cache' in your blocks table. The list of values are at top of block.module.

module

catch's picture

There's also a new module by swentel which provides a UI to the block caching settings - see http://drupal.org/project/blockcache_alter

That was it

elfur@drupal.org's picture

That was it.

This is what the site needed to fix page load times. But, the original fault is still underlying - calling for regeneration of the queries either as a custom module or within php blocks or something.

Again, thanks for all the help, it was enlightening :)

did you verify keys usage

Alexandr Kosarev-gdo's picture

did you verify keys usage with explain?

EXPLAIN SELECT COUNT(*) FROM (SELECT node.nid AS nid FROM node node INNER JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN feedapi_node_item_feed feedapi_node_item_feed ON node.nid = feedapi_node_item_feed.feed_item_nid LEFT JOIN feedapi_node_item feedapi_node_item ON feedapi_node_item_feed.feed_item_nid = feedapi_node_item.nid LEFT JOIN feedapi feedapi ON feedapi_node_item_feed.feed_nid = feedapi.nid LEFT JOIN node feed_node ON feedapi_node_item_feed.feed_nid = feed_node.nid WHERE (term_node.tid = 27) AND (node.type in ('feednode')) ) count_alias

and
EXPLAIN SELECT node.nid AS nid, node.title AS item_title, feedapi_node_item.url AS original_item_url, feed_node.title AS feed_title, feed_node.nid AS feed_nid, feedapi_node_item.timestamp AS feedapi_node_item_timestamp FROM node node INNER JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN feedapi_node_item_feed feedapi_node_item_feed ON node.nid = feedapi_node_item_feed.feed_item_nid LEFT JOIN feedapi_node_item feedapi_node_item ON feedapi_node_item_feed.feed_item_nid = feedapi_node_item.nid LEFT JOIN feedapi feedapi ON feedapi_node_item_feed.feed_nid = feedapi.nid LEFT JOIN node feed_node ON feedapi_node_item_feed.feed_nid = feed_node.nid WHERE (term_node.tid = 27) AND (node.type in ('feednode')) ORDER BY feedapi_node_item_timestamp DESC LIMIT 0, 30

I must admit, I'm new at

elfur@drupal.org's picture

I must admit, I'm new at this end of things. Using a query browser I pasted EXPLAIN in front of the query pair with the following results (exported as csv):

Q1:

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"PRIMARY","","","","","","",,"Select tables optimized away"
2,"DERIVED","term_node","ref","PRIMARY,vid","PRIMARY","4","",2300,"Using index"
2,"DERIVED","node","eq_ref","vid,node_type","vid","4","krepputal.term_node.vid",1,"Using where"
2,"DERIVED","feedapi_node_item_feed","index","","PRIMARY","8","",5852,"Using index"
2,"DERIVED","feedapi_node_item","eq_ref","PRIMARY","PRIMARY","4","krepputal.feedapi_node_item_feed.feed_item_nid",1,"Using index"
2,"DERIVED","feedapi","eq_ref","PRIMARY","PRIMARY","4","krepputal.feedapi_node_item_feed.feed_nid",1,"Using index"
2,"DERIVED","feed_node","eq_ref","PRIMARY","PRIMARY","4","krepputal.feedapi_node_item_feed.feed_nid",1,"Using index"

Q2

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","term_node","ref","PRIMARY,vid","PRIMARY","4","const",2300,"Using index; Using temporary; Using filesort"
1,"SIMPLE","node","eq_ref","vid,node_type","vid","4","krepputal.term_node.vid",1,"Using where"
1,"SIMPLE","feedapi_node_item_feed","index","","PRIMARY","8","",5852,"Using index"
1,"SIMPLE","feedapi_node_item","eq_ref","PRIMARY","PRIMARY","4","krepputal.feedapi_node_item_feed.feed_item_nid",1,""
1,"SIMPLE","feedapi","eq_ref","PRIMARY","PRIMARY","4","krepputal.feedapi_node_item_feed.feed_nid",1,"Using index"
1,"SIMPLE","feed_node","eq_ref","PRIMARY","PRIMARY","4","krepputal.feedapi_node_item_feed.feed_nid",1,""

I really don't know what this means, though.

Using temporary; Using

Jamie Holly's picture

Using temporary; Using filesort causes queries to really slow down. It could also be a major nightmare to get rid of with all the joins in that query. I looked at the install profile for feedapi. It appears you are running Drupal 5. Drupal 6's feedapi doesn't have that many tables. Upgrading (if feasible) could end up giving you much better query times. Also add an index on eedapi_node_item.timestamp, as I don't see that created in feedapi (or change your blocks to sort by the node created time).


HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.

Nope. I'm running Drupal 6.8

elfur@drupal.org's picture

Nope. I'm running Drupal 6.8 + feedAPI 6.x-1.5 (clean install, upgraded from ~6.6 and possibly a one or two sub-versions of feedAPI).

Using the same query builder as before, I can see the following tables related to feeds or nodes in my db:

feedapi
feedapi_mapper
feedapi_node_discussion
feedapi_node_item
feedapi_node_item_feed
feedapi_stat
node
node_acceess
node_comment_statistics
node_comments
node_counter
node_revisions
node_type
term_node

Judging by this conversation as a whole, I'm going to test altering the cache or else aiming for redefining the queries in a custom module.

Thanks for all the help guys.

Where is the rest of the time?

kbahey's picture

You have about 59,000 ms total, and those queries amount for 1,500 of that.

While that would help a lot, (start with the EXPLAIN as above and see what it tells you) it would not solve the problems completely. You will still be left with 44,000 worth of stuff to solve.

One way of solving this, if Views is indeed the issue, is to rewrite the block(s) as custom modules with less joins, and individual lookups if needed.

Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.

Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.

The rest of the time lies in

elfur@drupal.org's picture

The rest of the time lies in 12 views query pairs identical to the pair i pasted, where almost the only difference is the value of the taxonomy tid.

At another time, page load was:
Executed 192 queries in 54756.36 milliseconds. Queries taking longer than 5 ms and queries executed more than once, are highlighted. Page execution time was 55332.57 ms.

And the individual times of the 13 monster query pairs were:

         Q1          Q2
1     506.63     523.46
2     391.8      400.48
3    1839.43    1916.92
4    1019.07    1052.02
5    3400.75    3611.93
6    2243.02    2307.34
7    2938.28    3149.35
8    6376.22    6598.82
9    1388.78    1437.57
10   2601       2783.12
11   1306.96    1355.67
12    372.58     383
13   2344.78    2442.73

Follow up on my previous post

elfur@drupal.org's picture

Ok, so I've managed to have anonymous load times come down with the block cache alter, but I'm still looking for query optimization.

Therefore I've been analyzing the queries even further and comparing them with the tables in my db.
Looking at this now, I realize that in the query pair I pasted above, there's a call for feed_node.title and feed_note.nid even though there is no table in the dbschema called feed_node.

For the db/optimization novice that I am, this screams the question: How is that done? And, if the answer is temporary table, doesn't that affect load times? Might that be a/the culprit?

/elfur

feed_node is just the node

Jamie Holly's picture

feed_node is just the node table being aliased in the query:

LEFT JOIN node feed_node ON feedapi_node_item_feed.feed_nid = feed_node.nid WHERE (term_node.tid = 27) AND (node.type in ('feednode')) ) count_alias


HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.

block cache is for authenticated

catch's picture

Blocks are cached for authenticated users too.

For anonymous users you should already be using the page cache - which assuming the cache is already primed will load the entire page in one cache_get() anyway. So the block cache is going to save you everything except the initial generation of the blocks per role/page/user until the cache gets invalidated.

Simplifying Views queries

kbahey's picture

The feed_node is an alias in the query for the node table itself.

One solution is to break the query down.

Try running this query, which gets the same exact data as the original one without 2 extra joins. (the feed_title and feed_nid are the same as n.nid and n.title).

Do an explain on it and also verify that it returns the same data as the original query.

SELECT n.nid, n.title, fni.url, fni.timestamp
FROM node n
INNER JOIN term_node             tn   ON n.vid              = tn.vid
LEFT JOIN feedapi_node_item_feed fnif ON n.nid              = fnif.feed_item_nid
LEFT JOIN feedapi_node_item      fni  ON fnif.feed_item_nid = fni.nid
WHERE tn.tid = 27 AND n.type = 'feednode'
ORDER BY fni.timestamp
DESC
LIMIT 0, 30

Also measure the time for it vs. the original.

Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.

Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.

No, this is not the same data

elfur@drupal.org's picture

The thing is, as with most other sites, there are more than one node types stored in the node table.
With FeedAPI module, two node types are created, the feed and the feed_item.
I suspect that one of the joins on node.nid is for the feed and the other is for the feed item.
The view is displaying the following things:

Feed Item title (node.title - with a join on node.nid = fnif.feed_nid)
Feed Item url (fni.url)
Feed title (which is also feed item author - node.title - with a join on node.id = fnif.feed_item_nid)
Feed Node link (node.nid as a link)
Feed Item timestamp (fni.timestamp - different from node.created, because created is in my system, timestamp is original creation of feed item at original location)

While your suggestion is good, it only displays the Feed Item, without the author information.

Can be gotten individually

kbahey's picture

Well, that info can be retrieved with subsequent queries, after you have a list of nodes that match the filtering criteria.

So, one would have a loop that iterates through the nids that are retrieved and then get the additional info needed.

So rather than having one big query with many joins/left joins, we break that down into a smaller query with less joins, then individual lookups for the needed data, which will be using indexes and hence very fast.

But before you do that, let us have some quantification of how fast/slow my version is on your system vs. the original views generated query. You can run them from mysql command line and see how much time they take.

Also, running the EXPLAIN on both would be beneficial. And don't post it as CSV, just plain text.

Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.

Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.

OK, now I'm really confused

elfur@drupal.org's picture

I ran one of the 'culprit' queries directly within mysql (command line) and the result: 6 rows in set (0.70sec).

The query in question:

SELECT node.nid AS nid, node.title AS item_title, feedapi_node_item.url AS original_item_url, feed_node.title AS feed_title, feed_node.nid AS feed_nid, feedapi_node_item.timestamp AS feedapi_node_item_timestamp, node.created AS node_created FROM node node INNER JOIN term_node term_node ON node.vid = term_node.vid LEFT JOIN feedapi_node_item_feed feedapi_node_item_feed ON node.nid = feedapi_node_item_feed.feed_item_nid LEFT JOIN feedapi_node_item feedapi_node_item ON feedapi_node_item_feed.feed_item_nid = feedapi_node_item.nid LEFT JOIN feedapi feedapi ON feedapi_node_item_feed.feed_nid = feedapi.nid LEFT JOIN node feed_node ON feedapi_node_item_feed.feed_nid = feed_node.nid WHERE (term_node.tid = 6) AND (node.type in ('feednode')) ORDER BY node_created DESC LIMIT 0, 6

EXPLAIN on that query:

--- ###  ROW 1  ### ---
  id               1
  select_type      SIMPLE
  table            term_node
  type             ref
  possible_keys    PRIMARY,vid,tid
  key              PRIMARY
  key_len          4
  ref              const
  rows             143
  Extra            Using index; Using temporary; Using filesort

--- ###  ROW 2  ### ---
  id               1
  select_type      SIMPLE
  table            node
  type             eq_ref
  possible_keys    vid,node_type
  key              vid
  key_len          4
  ref              krepputal.term_node.vid
  rows             1
  Extra            Using where

--- ###  ROW 3  ### ---
  id               1
  select_type      SIMPLE
  table            feedapi_node_item_feed
  type             index
  possible_keys    NULL
  key              PRIMARY
  key_len          8
  ref              NULL
  rows             6829
  Extra            Using index

--- ###  ROW 4  ### ---
  id               1
  select_type      SIMPLE
  table            feedapi_node_item
  type             eq_ref
  possible_keys    PRIMARY
  key              PRIMARY
  key_len          4
  ref              krepputal.feedapi_node_item_feed.feed_item_nid
  rows             1
  Extra

--- ###  ROW 5  ### ---
  id               1
  select_type      SIMPLE
  table            feedapi
  type             eq_ref
  possible_keys    PRIMARY
  key              PRIMARY
  key_len          4
  ref              krepputal.feedapi_node_item_feed.feed_nid
  rows             1
  Extra            Using index

--- ###  ROW 6  ### ---
  id               1
  select_type      SIMPLE
  table            feed_node
  type             eq_ref
  possible_keys    PRIMARY
  key              PRIMARY
  key_len          4
  ref              krepputal.feedapi_node_item_feed.feed_nid
  rows             1
  Extra

now if I make sure that the query you pasted contains the same tid and the same limit amount, to return the same result (apart from feed name, of course), I get almost an identical result (again, mysql, command line): 6 rows in set (0.68sec).

Your query with the tiny alterations (27 > 6 and 30 > 6):

SELECT n.nid, n.title, fni.url, fni.timestamp
FROM node n
INNER JOIN term_node             tn   ON n.vid              = tn.vid
LEFT JOIN feedapi_node_item_feed fnif ON n.nid              = fnif.feed_item_nid
LEFT JOIN feedapi_node_item      fni  ON fnif.feed_item_nid = fni.nid
WHERE tn.tid = 6 AND n.type = 'feednode'
ORDER BY fni.timestamp
DESC
LIMIT 0, 6

EXPLAIN on that query:

---  ###  ROW 1  ###  ---
  id               1
  select_type      SIMPLE
  table            tn
  type             ref
  possible_keys    PRIMARY,vid,tid
  key              PRIMARY
  key_len          4
  ref              const
  rows             144
  Extra            Using index; Using temporary; Using filesort
    
---  ###  ROW 2  ###  ---
  id               1
  select_type      SIMPLE
  table            n
  type             eq_ref
  possible_keys    vid,node_type
  key              vid
  key_len          4
  ref              krepputal.tn.vid
  rows             1
  Extra            Using where

---  ###  ROW 3  ###  ---
  id               1
  select_type      SIMPLE
  table            fnif
  type             index
  possible_keys    NULL
  key              PRIMARY
  key_len          8
  ref              NULL
  rows             6829
  Extra            Using index

---  ###  ROW 4  ###  ---
  id               1
  select_type      SIMPLE
  table            fni
  type             eq_ref
  possible_keys    PRIMARY
  key              PRIMARY
  key_len          4
  ref              krepputal.fnif.feed_item_nid
  rows             1
  Extra    

What does all this tell me? That the culprit isn't the query itself? Or do I have to test this from within drupal, somehow?

Or is something I've done in the recent past working to the effect of speeding up the queries?
Which is extremely unlikely since if I try to hit the front page as user 1 it takes too long to reload and the webserver returnes a 404.

For comparison, results from Query Browser

elfur@drupal.org's picture

Just for comparison, here are result times using MySQL Query Browser:

  • the original query: 6 rows fetched in 0,101s (0,9252s)
  • your alteration: 6 rows fetched in 0,0070s (0,9217s)

Using this way to run the queries shows some difference in return times, it seems.
But I still don't know what all this means :)

Thanks again for all the help
/elfur

Hmmm

kbahey's picture

Although query browser is better, I think this is because the query result is in MySQL's query cache. You can turn it off temporarily so all queries are equal. You can do that by changing query_cache_type in my.cnf or interactively.

In reality, they are the same (0.70 vs. 0.68 seconds).

The main issue is this: Using temporary; Using filesort, and it is the first JOIN that is hurting us. But we need it to filter on terms.

We can try a subselect then.

SELECT n.nid, n.title, fni.url, fni.timestamp
FROM node n
LEFT JOIN feedapi_node_item_feed fnif ON n.nid              = fnif.feed_item_nid
LEFT JOIN feedapi_node_item      fni  ON fnif.feed_item_nid = fni.nid
WHERE n.type = 'feednode' AND
n.nid IN (SELECT tn.nid FROM term_node tn WHERE tn.tid = 6)
ORDER BY fni.timestamp
DESC
LIMIT 0, 6

So, turn the query cache off, then run both queries again, the original one, and the one above.

Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.

Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.

Here are the results

elfur@drupal.org's picture

running from command line with query_cache_type set as off - the original query: 0.71 sec - the latest modified query: 0.85 sec
running from MySQL Query Browser: the orignial query: 0,0024s (1,2297s) - the latest modified query: 0,0044s (1,1746s)

Explain on the latter, as we know the former:

---  ###  ROW 1  ###  ---
  id               1
  select_type      PRIMARY
  table            n
  type             range
  possible_keys    node_type
  key              node_type
  key_len          14
  ref              NULL
  rows             6895
  Extra            Using where; Using temporary; Using filesort
   
---  ###  ROW 2  ###  ---
  id               1
  select_type      PRIMARY
  table            fnif
  type             index
  possible_keys    NULL
  key              PRIMARY
  key_len          8
  ref              NULL
  rows             6978
  Extra            Using index

---  ###  ROW 3  ###  ---
  id               1
  select_type      PRIMARY
  table            fni
  type             eq_ref
  possible_keys    PRIMARY
  key              PRIMARY
  key_len          4
  ref              krepputal.fnif.feed_item_nid
  rows             1
  Extra           

---  ###  ROW 4  ###  ---
  id               2
  select_type      DEPENDENT SUBQUERY
  table            tn
  type             index_subquery
  possible_keys    PRIMARY,nid,tid
  key              nid
  key_len          4
  ref              func
  rows             2
  Extra            using where

LEFT JOIN -vs- ORDER BY ...hmm

markus_petrux's picture

I noticed that you're sorting on a column that's from a LEFT JOIN'd table... not sure if that's what you really need. If you're interested in rows that have that relation, then an INNER JOIN could make things easier. You could then alter the order of the joins, starting from the table that have less records (or that can be filtered faster), and you could even use STRAIGHT_JOIN to force the optimizer to go the route you wish.

Sadly, if I recall correctly, MySQL can not use more than one index per table to resolve a query. So it might not be possible to get the desired results.

Good point ...

kbahey's picture

Good point. Worth a try.

The main problem is the INNER JOIN of term_node, which causes the temporary table and a file sort.

Let us try to eliminate that, then see what else can be done.

Worse case is to retrieve the timestamp and then do a sort in PHP by that field if need be.

Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.

Drupal performance tuning, development, customization and consulting: 2bits.com, Inc..
Personal blog: Baheyeldin.com.

Cause of filesort

markus_petrux's picture

I believe the cause of the filesort is the ORDER BY clause. If you remove that, maybe then there is no filesort.

Maybe the ideal solution would have to involve an index that can be used to resolve the joins, but also the order by clause.

You're right

elfur@drupal.org's picture

Removing the 'order by' clause from the query gives the following result:
using Query Browser: 6 rows fetched in 0.0021s (0.3073s) <-- this is the lowest value within bracket I've seen, yet.

The query:

SELECT
   node.nid AS nid,
   node.title AS item_title,
   feedapi_node_item.url AS original_item_url,
   feed_node.title AS feed_title,
   feed_node.nid AS feed_nid,
   feedapi_node_item.timestamp AS feedapi_node_item_timestamp,
   node.created AS node_created
FROM node node
INNER JOIN term_node term_node ON node.vid = term_node.vid
LEFT JOIN feedapi_node_item_feed feedapi_node_item_feed ON node.nid = feedapi_node_item_feed.feed_item_nid
LEFT JOIN feedapi_node_item feedapi_node_item ON feedapi_node_item_feed.feed_item_nid = feedapi_node_item.nid
LEFT JOIN feedapi feedapi ON feedapi_node_item_feed.feed_nid = feedapi.nid
LEFT JOIN node feed_node ON feedapi_node_item_feed.feed_nid = feed_node.nid
WHERE (term_node.tid = 6) AND (node.type in ('feednode'))
LIMIT 0, 6

This would be good, if it returned the correct data :)
This returns the most recent values of the least updated blog feed in the feedapi. If you go to the last/second last page of this view: http://krepputal.elfur.org/hoparmanna you find the records that this query returns.

-- but it does remove the 'using filesort' :)

I've got a solution, finally

elfur@drupal.org's picture

To follow up on all the discussion that has taken place here, I've found my solution.

I'm creating custom blocks to display the information I intended to have views create for me (still using the page view of the same data though). The code I wound up with (I split the query in two, based on kbahey's suggestion):

$result = db_query("SELECT node.nid AS nid, node.title AS item_title,
    feedapi_node_item.url AS original_item_url,
    feedapi_node_item.timestamp AS feedapi_node_item_timestamp
    FROM node node
    INNER JOIN term_node term_node ON node.vid = term_node.vid
    LEFT JOIN feedapi_node_item feedapi_node_item ON
    node.nid = feedapi_node_item.nid
   WHERE (term_node.tid = 5) AND (node.type in ('feednode'))
   ORDER BY feedapi_node_item_timestamp DESC LIMIT 0,6");
$output = '<ul>';
while ($feed = db_fetch_object($result)) {
   $sql_feed_parent = db_query("SELECT node.title AS feed_title,
   node.nid AS feed_nid FROM node node
   INNER JOIN feedapi_node_item_feed fnif ON node.nid = fnif.feed_nid
where fnif.feed_item_nid = '".$feed->nid."'");

And I created two indices, one on timestamp in the feed_note_item table and the other on feed_item_nid in the feed_node_item_feed table - the first of the two indices really enhanced the overall speed of the site.

All this returns fabulous load times (the earlier query maxes at 40ms every few loads, and stays under 1ms on other loads - the other six are always below 1ms). After I created the timestamp index, the load times of the original queries range from 20-250ms, which is semi acceptable, but as these are faster I'm still converting the rest.

The only question is ... how expensive are the six extra db calls per block?

High performance

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds: