By elfur on

I've built my front page by displaying several views. Many of which are selecting from an ever increasing database table (node). It is taking longer and longer to load the page so I installed devel and have results indicating that a query is calling for all records in certain criteria, when all I need (and ask for while building the view) are the top 6 records. For that matter, why are there two queries for each view?

These are amongst the devel query results:

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.
..
(Q1) 
506.63	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 = 6) AND (node.type in ('feednode')) ) count_alias

(Q2) 
523.46	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, 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

...
Memory used at: devel_init()=0.24 MB, devel_shutdown()=8.06 MB.

As the devel result says, it's 192 queries in total but the 26 that are like the two displayed above, take the longest, by far (54691.71ms out of 54756.36 total).

I display 14 views and have records like the last two for 13 of them with query-time as follows:

         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

Running drupal 6.8 and views 6.x-2.2

⋅ Categories: Drupal 6.x

Comments

Dave.Ingram’s picture

Dave.Ingram commented

Hi there,

Your first query is getting a count of how many 'feednode' nodes are in that taxonomy term.
Then your second query gets the node id, title, url of feeds, etc, and it is only getting 6 of them at a time as you said. (that's the 'LIMIT 0, 6' right at the end.

I guess the first query is because you're either displaying a count of total nodes somewhere, or it is being used for a pager or something of that sort. You could try turning off pagers in your view, but this might defeat the purpose of your page. As far as optimizing it, it's joining together a whole bunch of tables to get that result, but you'll need someone very skilled in MySQL and Views to help you work that one out.

Good luck!

Dave Ingram - Gainesville, FL
www.ingraminnovation.com

elfur’s picture

elfur commented

right, ok. That answers one of my questions. I'm not using pager, but I am using the "more" link and that might have been the culprit, am still debugging.
If so, I apparantly need a different method for publishing the more link, because calling 13 queries at some hundred or even thousand milliseconds each, is simply unacceptable.

That still leaves the question why the hell the query takes some hundred or even thousand milliseconds to run (pardon my french).

/elfur.is

elfur’s picture

elfur commented

To followup on my previous post, it is the more link that is causing the extra query. Which, to me, begs the question: isn't it an overkill to call a query like that simply for the more link?

/elfur.is