Pagination using SQL scripts leads to heavy load

The problem comes from this part of code:

function template_preprocess_forum_topic_navigation(&$variables) {
  $output = '';

  // get previous and next topic
  $sql = "SELECT n.nid, n.title, n.sticky, l.comment_count, l.last_comment_timestamp FROM {node} n INNER JOIN {node_comment_statistics} l ON n.nid = l.nid INNER JOIN {term_node} r ON n.nid = r.nid AND r.tid = %d WHERE n.status = 1 ORDER BY n.sticky DESC, ". _forum_get_topic_order_sql(variable_get('forum_order', 1));
  $result = db_query(db_rewrite_sql($sql), isset($variables['node']->tid) ? $variables['node']->tid : 0);

  $stop = $variables['prev'] = $variables['next'] = 0;

  while ($topic = db_fetch_object($result)) {
    if ($stop == 1) {
      $variables['next'] = $topic->nid;
      $variables['next_title'] = check_plain($topic->title);
      $variables['next_url'] = url("node/$topic->nid");
      break;
    }
    if ($topic->nid == $variables['node']->nid) {
      $stop = 1;
    }
    else {
      $variables['prev'] = $topic->nid;
      $variables['prev_title'] = check_plain($topic->title);
      $variables['prev_url'] = url("node/$topic->nid");
    }
  }
}

db_fetch_object($result) should not be used to fetch and analyse 21.000 records.

I cannot fix this PHP function and leave this to core developers.

Setting to critical as no serious forum can work with kind of query.

Sorry, I apologize.
I modified all recent messages to correct them.

This message is part of a tutorial which I am writing here:
Guidelines for writing efficient SQL code

Kind regards,
Jean-Michel

Can you modify the title of this message to:
"Drupal query returns 21000 rows, which are computed using PHP"

Why cannot-it be fixed?
Is this a no-op for you?

Really thank you, I am relieved.
I will transfer bug to the theme maintainer.

Do you think it can be used:
http://drupal.org/project/prev_next

The bug was closed by Damien and handed to the theme developers by myself. I hope that D7 offers such a feature, integrated. It not normal to query 21.000 rows just for the sole display of Previous/Next and compute them in PHP one by one.

Now I have to download http://drupal.org/project/prev_next and modify Drupal core. I am getting lost.

I have to admit strange things are going on in Drupal and don't blame me. I am not a troll: I participated in pgAdmin3 development, kdenlive video editor. I optimized several PHPBB SQL queries for scalibility and I am quite amazed by the low-level of attention to this previous/next link. And more over to database performance. I did my best and spent a couple of days reviewing Drupal problems under PostgreSQL and wrote two guides: http://drupal.org/node/559302 and http://drupal.org/node/555514.

Previous/next is one the bases of the web. Many people concentrate on cache here, people discuss about farms, but is it really needed if a framework cannot scale Prev/Next to 650.000 records. There are SQL design problems to handle quickly. Again, I hope this is fixed in D7.

My website is going live in a few days. If I publish it with Drupal current internals, it may explode. Now I am prepared for anything. Of course, I will disable prev/next link.

Can we discuss what could be done for Prev/Next navigation to get it into core Drupal 6-dev forum. Or is this impossible by nature of Drupal coding?

I would like to escalate directly to Dries Buytaert and pledge him to ask the community to fix this prev/next problem, quickly. Several sites may be suffering from heavy load problems. If admins do not have access to MySQL or PostgreSQL settings, they may not be able to log queries server-side and thus will never discover this simple pagination problem.

This is the problem of using tools like MySQL. You never really know what is going on. People are discussing about caching MySQL, but Previous/Next is not fixed in the drupal forum. I can only encourage your teams to read my two guides and migrate to PostgreSQL. Today, drupal.org became very slow. Why not migrate to PostgreSQL and log all slow query plans. This would allow you to optimize Drupal quickly. This is not a joke, I really believe it. If you are interested, I can help.

Dries, please, help, what is your opinion? Can you get scalable Prev/Next links in Drupal forum? With not provide a scalable API for Prev/Next within Drupal. Are there reason?

Kind regards,
Jean-Michel

Finally, I decided to reopen this bug and escalate to Dries directly.

Damien, I still don't believe why you closed that bug.
This is a heavy problem in forum and closing the bug will not remove problems.

Thanks, I got confirmation from the dev list that this issue would be studied and probably fixed. Thank you very much Drupalers.

Pardon me, but I am very new to Drupal API to I might write nonsence.
Presently I am working like hell to verify SQL scalibility reading server-side queries.

For previous, current and next record, why not use a SQL combination of LIMIT and OFFSET?

This makes it possible to fetch only three records:

• Previous (n-1)
• Current (n)
• Next (n+1)

SELECT select_list
FROM table_expression
[ ORDER BY ... ]
LIMIT 3 OFFSET n-1

Where n is the record that you would like.

http://www.postgresql.org/docs/8.4/interactive/queries-limit.html

Again, I don't know how to handle that in Drupal, but it ***seems*** to make sense to use LIMIT OFFSET
Usually the database is able to use indexes and access data directly, thus it can be lightning fast with no overhead.

We can always check the detailed plans.

But it seems more reasonable to run 100 queries with LIMIT OFFSET and store results in cache
rather than sending ONE huge SQL Query to the database.

LIMIT OFFSET scales to all databases.

Generally, it is more interesting to run a collection of queries returning 1 to 10 records max that running 1 huge SQL query and storing it in cache for later PHP processing. Processing 21.000 records in PHP demands too much PHP memory. This can drive performance down.

Running multiple short SQL queries returning 1 to 10 records scales better.
In the case of LIMIT 3 OFFSET n-1 it is pretty obvious.

What is your opinion?

For example, I run this arbitrary query fetching 3 records out of 21.000 thousands in 266 ms:

SELECT n.nid, n.title, n.sticky, l.comment_count, l.last_comment_timestamp 
FROM node n 
INNER JOIN node_comment_statistics l ON n.nid = l.nid 
INNER JOIN term_node r ON n.nid = r.nid AND r.tid = 9 
WHERE n.status = 1 
ORDER BY n.sticky DESC, l.last_comment_timestamp DESC 
LIMIT 3 OFFSET 14420

This seems very slow.

Let us add an index:

CREATE INDEX node_sticky_idx
ON node (sticky)

Now, the query plan is:

"Limit  (cost=7523.73..7523.74 rows=3 width=57) (actual time=167.907..167.908 rows=3 loops=1)"
"  ->  Sort  (cost=7487.68..7540.42 rows=21096 width=57) (actual time=164.617..166.831 rows=14423 loops=1)"
"        Sort Key: n.sticky, l.last_comment_timestamp"
"        Sort Method:  quicksort  Memory: 3335kB"
"        ->  Hash Join  (cost=4264.89..5972.50 rows=21096 width=57) (actual time=73.043..113.917 rows=21081 loops=1)"
"              Hash Cond: ((l.nid)::integer = n.nid)"
"              ->  Seq Scan on node_comment_statistics l  (cost=0.00..1074.86 rows=67486 width=12) (actual time=0.004..7.980 rows=67486 loops=1)"
"              ->  Hash  (cost=4001.19..4001.19 rows=21096 width=53) (actual time=73.021..73.021 rows=21081 loops=1)"
"                    ->  Hash Join  (cost=1292.15..4001.19 rows=21096 width=53) (actual time=12.306..65.201 rows=21081 loops=1)"
"                          Hash Cond: (n.nid = (r.nid)::integer)"
"                          ->  Seq Scan on node n  (cost=0.00..1907.58 rows=67486 width=49) (actual time=0.003..24.152 rows=67486 loops=1)"
"                                Filter: (status = 1)"
"                          ->  Hash  (cost=1028.45..1028.45 rows=21096 width=4) (actual time=12.272..12.272 rows=21081 loops=1)"
"                                ->  Bitmap Heap Scan on term_node r  (cost=399.75..1028.45 rows=21096 width=4) (actual time=1.939..8.014 rows=21081 loops=1)"
"                                      Recheck Cond: ((tid)::integer = 9)"
"                                      ->  Bitmap Index Scan on term_node_tid_idx  (cost=0.00..394.48 rows=21096 width=0) (actual time=1.881..1.881 rows=21081 loops=1)"
"                                            Index Cond: ((tid)::integer = 9)"
"Total runtime: 168.508 ms"

This query can execute in 168ms

Notice a cast from int_unsigned to int happens.
This should not be the case and I contacted PostgreSQL hackers for that problem.

Because of ORDER BY, we cannot avoid a sequential scan or a sort in memory.
i believe that this query should run more quickly when the CAST problem is fixed.

PostgreSQL hackers believe that this is a problem with my install.