I have set an account on my site to default to the Chinese language. The front page (www.example.com/zh-hans) for this account takes a really long time to load in normal circumstances because it is trying to do the following query on an innodb table:

mysql> explain SELECT COUNT(*) FROM node n LEFT JOIN node i18n ON n.tnid > 0 AND n.tnid = i18n.tnid AND i18n.language = 'zh-hans' WHERE ((n.moderate != 1 OR n.uid = 511)) AND (n.language ='zh-hans' OR n.language ='' OR n.language IS NULL OR n.language = 'en' AND i18n.nid IS NULL) AND ( n.promote = 1 AND n.status = 1 ); +----+-------------+-------+------+--------------------------------------------------------+---------------------+---------+--------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+--------------------------------------------------------+---------------------+---------+--------------+-------+-------------+ | 1 | SIMPLE | n | ref | uid,node_moderate,node_promote_status,node_status_type | node_promote_status | 8 | const,const | 755 | Using where | | 1 | SIMPLE | i18n | ref | tnid | tnid | 4 | szp6c.n.tnid | 17886 | Using where | +----+-------------+-------+------+--------------------------------------------------------+---------------------+---------+--------------+-------+-------------+ 2 rows in set (0.00 sec)

I believe this is a pager query trying to determine the number of nodes to available for the front page and how many pages to display at the bottom.

From the documentation I have read it is not good to so a SELECt Count(*) on an innodb table because it has to recalculate everything as it doesn't store this kind of information. See: http://2bits.com/articles/mysql-innodb-performance-gains-as-well-as-some...

When loading our English front page we don't have any delay. I supposed the query is much simpler.

How do I avoid this problem without needing to create a brand new home page which does not contain this pager query?

I have quite a large innodb cache and my query cache is turned on. Has anyone had this problem and created a patch to address it?

Comments

ddorian’s picture

ddorian commented

subscribe

damien tournoud’s picture

damien tournoud commented
Title: Non Deftault language Front Page Loads Slowly because of Select Count(*) with table in innodb format » Non Default language Front Page Loads Slowly because of Select Count(*) with table in innodb format
Project: Drupal core » Internationalization
Version: 6.10 » 6.x-1.x-dev
Component: language system » Code

This query is rewritten by i18n.

deverman’s picture

deverman commented

OK, anyone have any suggestions on how to solve this problem?

deverman’s picture

deverman commented

I still have not figured out a fix for this so I tried to make a new front page using views and nodequeues so that I could remove the pager query. Unfortunately I ran into a problem with nodequeues and translations which are also not working. I really would like to use drupal for multilingual content but it is completely failing me.

Here is my nodequeue problem:

http://drupal.org/node/433930

Here is another problem with translations of interface content not working:

http://drupal.org/node/429822

deverman’s picture

deverman commented

We have started to build a new home page based on views and node queue to avoid this problem but we have run into a similar problem when we try to go to the forum section of our site. We get stuck on this query:

/ forum_get_forums / SELECT r.tid, COUNT(n.nid) AS topic_count, SUM(l.comment_count) AS comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN term_node r ON n.vid = r.vid LEFT JOIN node i18n ON n.tnid > 0 AND n.tnid = i18n.tnid AND i18n.language = 'zh-hans' WHERE (n.language ='zh-hans' OR n.language ='' OR n.language IS NULL OR n.language = 'en' AND i18n.nid IS NULL) AND ( n.status = 1 )GROUP BY r.tid

I think it is the same problem that it is trying to do this Count() on an innodb table. I believe it is necessary for us to be in innodb format.

We can't be the only ones having these types of problems? I am being completely blocked from launching a multilingual web site thanks to drupal. I choose drupal because I had seen other web sites running with multiple languages in drupal what are we doing wrong that we keep running into these road blocks?

jose reyero’s picture

jose reyero commented
Status: Active » Closed (duplicate)

Pager queries need a count. Anyway, about that specific query...#337089: Mixed mode ultra slow on large db