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
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?
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
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
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
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
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
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
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_aliasand
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, 30I must admit, I'm new at
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
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
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?
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
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 Q21 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
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.titleandfeed_note.nideven 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
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
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
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.timestampFROM 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
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
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
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, 6EXPLAIN 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.timestampFROM 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
Just for comparison, here are result times using MySQL Query Browser:
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
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.timestampFROM 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
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
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 ...
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
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
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:
SELECTnode.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
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?