Mixed mode ultra slow on large db

The query for mixed mode is really complex so this is to be expected.

Check out the actual queries (devel module) and let us know if you can think of a faster query (I can't)....

Optionally, think of the simpler selection mode (current language only) and create dummy translations (untranslated copys) for your content...

Not sure I understand.

I agree the query is complex and may be slow.

Do you mean you have a simpler/faster alternate query for getting the same? If so can I see two full queries or a patch, please, so I can compare?

That won't even produce the same result for 'mixed' mode, it will print duplicate content. Try printing out the actual query.

So, unless someone can give me an example of a better query for the same, nothing to do here.

Yes, 'mixed' mode is slow because the query needed is complex.

Perhaps it could help to replace the left join with a subquery. The OR condition could then look like:

" OR ($primary_table.language = '" . i18n_default_language() . "' AND NOT EXISTS (SELECT {node} i18n WHERE $primary_table.tnid = i18n.tnid AND i18n.language = '".i18n_get_lang()."'))"

This is not tested and I am not sure if I got all the quotes right :-)... Just submitting the idea.

Someone provides a better query with some benchmarking, we'll fix it.

I'm just not sure there is one (faster query)

It is not as much a problem with the query as the lack of indexes. Here is what I did...

Examine this query:

SELECT COUNT(*) FROM node n  
LEFT JOIN node i18n 
  ON n.tnid > 0 
    AND n.tnid = i18n.tnid 
    AND i18n.language = 'es' 
WHERE (n.language ='es' 
  OR n.language ='' 
  OR n.language IS NULL 
  OR n.language = 'es' 
  AND i18n.nid IS NULL) 
  AND (  n.promote = 1 AND n.status = 1 )   ;

Originally, it was using where,filesort, on 750k rows altogether. I didn't record the explain (sorry). It was horrific. I think it was the same as this one:

+----+-------------+-------+------+--------------------------------------------------------+---------------------+---------+--------------+-------+-------------+ | 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 | +----+-------------+-------+------+--------------------------------------------------------+---------------------+---------+--------------+-------+-------------+

Now it returns:

+----+-------------+-------+-------+---------------+---------+---------------------------------+------+-------------+
| id | select_type | table | type  | key           | key_len | ref                             | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------------------------------+------+-------------+
|  1 | SIMPLE      | n     | range | promote       | 46      | NULL                            |  606 | Using where | 
|  1 | SIMPLE      | i18n  | ref   | tnid_language | 42      | live_dbname.n.tnid,const        |   15 | Using where | 
+----+-------------+-------+-------+---------------+---------+---------------------------------+------+-------------+

To get this performance increase, I added:
alter table node add key(promote,status,language); <- This is the promote key. Badly named, I know...
alter table node add index tnid_language(tnid,language);

The first key fixes the first part of the query which was returning several thousands of rows, the second fixes the join conditions.

If we can make a quick modification to the schema, this is enough to keep things running. I also recommend a VERY large query cache, since it will help a lot here. I have 17k nodes or so, and this query takes a couple of seconds to return (it was taking several minutes before).

Having very similar problems and taking inspiration from #9, the following alteration to indexes reduces typical query execution time from 42 sec to 7 sec, but I can't see a better way of doing the join while still returning the same results - we're investigating the implications of not using the 'mixed' mode.

ALTER TABLE `node` DROP INDEX `node_promote_status`; 
ADD INDEX `node_promote_status` ( `promote` , `status` , `language` );
ALTER TABLE `node` DROP INDEX `tnid`; 
ADD INDEX `tnid` ( `tnid` , `language` ) ;

Great solution (#9 and #10) !
The queries generated by i18n on "mixed" are very very slow when you have a node table with more than 20000 rows.

Some results appling #9:

NODE SEARCH RESULTS:
-----------------------
BEFORE #9: 80 seconds (approx) tooks mysql to give the results ( making the php process die before the results )
AFTER: #9: 4 seconds (or less)
=> 20 times faster !

I Will test what happen creating new nodes, that may be a little slower ( teorically )..
(any problems will be posted here)
Thanks !

Hi everyone,

maybe I'm wrong or my solution does not apply to all situations but I think there is a faulty join statement in the i18n.module:

$result['join'] = "LEFT JOIN {node} i18n ON $primary_table.tnid = i18n.tnid AND i18n.language = '".i18n_get_lang()."'";
 

should actually be

$result['join'] = "LEFT JOIN {node} i18n ON $primary_table.tnid = i18n.nid AND i18n.language = '".i18n_get_lang()."'"; 

The code snippet mentioned can be found in file i18n.module on line 430.

In detail:

The join with the second instance of node has to combine column $primary_table.tnid with column i18n.nid and not with column i18n.tnid. I tested my solution successfully with drupal 6.16, the Internationalization module version 6.x-1.4 and biblio 6.x-1.10 with a content of about 5000 nodes. The join in mixed mode happens when I switch the language from default to another language. In this case it is the count-query passed to the pager_query function which comes out wrong and takes too much time. This does not result in hyper performance SQL but the language switch now performs with an acceptable response time.

Regards

Marco

sounds really great, but

mysql> SELECT DISTINCT COUNT(n.nid) FROM node n  INNER JOIN node_access na ON na.nid = n.nid LEFT JOIN node i18n ON n.tnid > 0 AND n.tnid = i18n.nid AND i18n.language = 'en' WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public'))) AND (n.language ='en' OR n.language ='' OR n.language IS NULL OR n.language = 'de' AND i18n.nid IS NULL) AND (  n.status = 1);
+--------------+
| COUNT(n.nid) |
+--------------+
|        16981 |
+--------------+
1 row in set (0.44 sec)

is not the same as

mysql> SELECT DISTINCT COUNT(n.nid) FROM node n  INNER JOIN node_access na ON na.nid = n.nid LEFT JOIN node i18n ON n.tnid > 0 AND n.tnid = i18n.tnid AND i18n.language = 'en' WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public'))) AND (n.language ='en' OR n.language ='' OR n.language IS NULL OR n.language = 'de' AND i18n.nid IS NULL) AND (  n.status = 1);
+--------------+
| COUNT(n.nid) |
+--------------+
|        16979 |
+--------------+
1 row in set (34 min 36.75 sec)

on my site there are only two nodes which have a translated version. These seem to be counted twice.

What about turning around the condition?

mysql> SELECT DISTINCT COUNT(n.nid) FROM node n  INNER JOIN node_access na ON na.nid = n.nid LEFT JOIN node i18n ON n.tnid > 0 AND n.nid = i18n.tnid AND i18n.language = 'en' WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 0 AND na.realm = 'og_public'))) AND (n.language ='en' OR n.language ='' OR n.language IS NULL OR n.language = 'de' AND i18n.nid IS NULL) AND (  n.status = 1);
+--------------+
| COUNT(n.nid) |
+--------------+
|        16979 |
+--------------+
1 row in set (0.44 sec)

That produces the right result in my case

is this still in the working queue?
From my point of view #13 solves the problem, but the old query is launched again.

sorry, forgot to append the patch explicitly.

You might want to look at the active_translation module. It was designed to store the selection in a table so that it could be simply queried with a single inner join.

I'm not sure to understand what you want to tell me.
As far as I can see, the module you mentioned is not a substitute for i18n since the latter one has a couple of dependencies to other modules installed on my site. And "active_translation" is currently not known to be ported to D7 (and so might be dropped in future).
On the other hand: If the patch I provided does it's job, why not applying it?

I hope that this gets resolved. We have ended up upgrading the server memory to the maximum physical RAM that it supports (8 gigabytes). And guess what, that wasn't enough. This slow query occupied the whole memory right away. We have well over 200,000 nodes. But as soon as we disable i18n or disable this particular query, everything single becomes blazing fast.

edit: Shame on me. I didn't see the patch in this thread. I hope it works.

Cheers

Maestro

please tell if it helps and produces the result you expected.

It seems quite faster now and the slow query log file is empty so far. I will check the page translations to make sure that they show up as expected.

Thanks

Maestro

Yes, the result seems to be the one expected. It is hard to say for sure because I have a very large DB.

Thanks

Maestro

when upgrading to version i18n-6.x-1.7 please remember to apply the slow query patch again (adjust line 452 in i18n.module).

I applied this patch to a site with a large DB and it did make a big difference in performance.

on my site the query runs about 5000 times faster

#12 - #15 this is unfortunately not a generic solution.

You cannot make this assumption about the value of 'tnid'. It could be a different value from the 'nid' on either side of the join.
(Suppose your current language is French, and the default is English. The JOIN on {i18n} is supposed to join the French & English translations of the nodes, whose 'tnid' is the same. But for all we know, the value of that 'tnid' might as well be the nid of the German translation. You'll get wrong query output in that case.)

Someone provides a better query with some benchmarking, we'll fix it.

Well, I just increased speed by a hundredfold (from 5.5s to 55ms), for one particular situation. So I won't provide benchmarks; the speed gain is obvious. I'll just outline the particular situation...and add that I don't think this change has adverse effects. I probably should do overall query benchmarks on my whole site; sorry.

My situation:
- My site has a 'library' of content in different languages, for which it needs 'mixed mode'...
- and a forum, which has grown quite large. Forum nodes are not translatable and don't even have a language attached. So, all forum nodes have tnid == 0.

When you try to load www.SITENAME/de/forum, i.e. the forum overview (of nodes which are all language neutral) in the non-default presentation language, it's horribly horribly slow.

The culprit is a query in advanced_forum.module that's run for every available subforum.
Apparently it's taking a very long time joining {i18n}, which has records where all tnids are 0 anyway (i.e. they are not needed).

When I change this join to i18n...

SELECT DISTINCT n.nid, ...
            FROM node n
            INNER JOIN term_node tn ON n.vid = tn.vid
            INNER JOIN ...
              LEFT JOIN node i18n ON n.tnid > 0 AND n.tnid = i18n.tnid AND i18n.language = 'de' 
WHERE (n.language ='de' OR n.language ='' OR n.language IS NULL OR n.language = 'en' AND i18n.nid IS NULL) 
AND (  n.status = 1 AND tn.tid = SOME-SUBFORUM-TID)

to a join on a subquery (which returns no/very little records)...

SELECT DISTINCT n.nid, ...
            FROM node n
            INNER JOIN term_node tn ON n.vid = tn.vid
            INNER JOIN ...
              LEFT JOIN (SELECT tnid FROM node WHERE tnid <> 0 AND language='de') i18n ON n.tnid = i18n.tnid
WHERE (n.language ='de' OR n.language ='' OR n.language IS NULL OR n.language = 'en' AND i18n.tnid IS NULL) 
AND (  n.status = 1 AND tn.tid = SOME-SUBFORUM-TID)

...the query executes in 1/100th of the time.

Hello roderik

thanks for your efforts. I tried to compare results to the query I proposed

query from #13

SELECT DISTINCT COUNT(n.nid) 
  FROM node n
    INNER JOIN node_access na ON na.nid = n.nid
    LEFT JOIN node i18n ON n.tnid > 0 AND n.nid = i18n.tnid AND i18n.language = 'en'
  WHERE (      na.grant_view >= 1
         AND ((na.gid = 0 AND na.realm = 'all')
                  OR (na.gid = 0 AND na.realm = 'og_public')
                )
        )
    AND (   n.language ='en'
         OR n.language =''
         OR n.language IS NULL
         OR n.language = 'de'
         AND i18n.nid IS NULL
        )
    AND (  n.status = 1);
+--------------+                                                                                                             
| COUNT(n.nid) |                                                                                                             
+--------------+                                                                                                             
|        21949 |                                                                                                             
+--------------+                                                                                                             
1 row in set (1.04 sec) 

compared to a query with sub-queries as you proposed

SELECT DISTINCT count(n.nid)
  FROM node n
    INNER JOIN node_access na ON na.nid = n.nid
    LEFT JOIN ( SELECT tnid FROM node 
                WHERE tnid <> 0 AND language='de'
              ) i18n 
              ON n.tnid = i18n.tnid
    WHERE 
      (      na.grant_view >= 1
         AND ((na.gid = 0 AND na.realm = 'all')
               OR (na.gid = 0 AND na.realm = 'og_public')
             )
      )
      AND
     (   n.language ='de' 
      OR n.language ='' 
      OR n.language IS NULL 
      OR n.language = 'en' 
      AND i18n.tnid IS NULL
     )
     AND (  n.status = 1 );
+--------------+
| count(n.nid) |
+--------------+
|        21949 |
+--------------+
1 row in set (0.91 sec)

yields in same results on my site. But your proposal seems to be a bit faster.

And surprisingly it doesn't matter if the condition is "AND language='de'" or "AND language='en'".

Thanks for testing. Your figures will mean more than mine... for a real example of content that's got a language assigned. (I have hardly any actually translated nodes at the site I was testing.)

I was running de as default language and de-AT as secondary language. When opening the forum as user with mydomain.at, the page load took a lot of time (about 5 minutes). The page load increased dramatically after adding the patch in #26.

Before:
# Time: 110608 17:58:04
# User@Host: community[community] @ localhost []
# Query_time: 11.746006 Lock_time: 0.000113 Rows_sent: 1 Rows_examined: 13230368
SET timestamp=1307548684;
SELECT DISTINCT n.nid,
n.title as node_title,
n.type,
ncs.last_comment_timestamp as timestamp,
IF (ncs.last_comment_uid != 0, u2.name, ncs.last_comment_name) AS name,
ncs.last_comment_uid as uid
FROM node n
INNER JOIN users u1 ON n.uid = u1.uid
INNER JOIN term_node tn ON n.vid = tn.vid
INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid
INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid
INNER JOIN node_access na ON na.nid = n.nid LEFT JOIN node i18n ON n.tnid > 0 AND n.tnid = i18n.tnid AND i18n.language = 'de-AT' WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 5298 AND na.realm = 'content_access_author') OR (na.gid = 2 AND na.realm = 'content_access_rid') OR (na.gid = 0 AND na.realm = 'og_public') OR (na.gid = 2 AND na.realm = 'term_access'))) AND (n.language ='de-AT' OR n.language ='' OR n.language IS NULL OR n.language = 'de' AND i18n.nid IS NULL) AND ( n.status = 1 AND tn.tid = 97
)ORDER BY ncs.last_comment_timestamp DESC LIMIT 0, 1;

it looks that the patch from #26 is not applied.

Nope, patch is not applied.

Please see #1252494: Create a new module for complex query altering (mixed mode) language selection.

Apologies for posting on an old issue but I found a performance fix for i18n that worked on my site, and I wanted to share it here just in case anyone else found it useful.

The site is a medium-sized international news site and certain queries were bogging down completely (1 minute + to execute). These were always something to do with i18n. Some queries were improved by adding an index to the "language" field on the node table but this didn't help others.

The fix I eventually found was to modify the i18n_db_rewrite_where() function, so that instead of using the equals operator for string comparison of the language code, it uses LIKE. So:

function i18n_db_rewrite_where($alias, $type, $mode = NULL) {
  if (!$mode) {
    // Some exceptions for query rewrites.
    $mode = i18n_selection_mode();
  }

  // Get languages to simplify query building.
  $current = i18n_get_lang();
  $default = i18n_default_language();

  if ($mode == 'strict' && $type != 'node') {
    // Special case. Selection mode is 'strict' but this should be only for node queries.
    $mode = 'simple';
  }
  elseif ($mode == 'mixed' && $current == $default) {
    // If mode is mixed but current = default, is the same as 'simple'.
    $mode = 'simple';
  }

  switch ($mode) {
    case 'off':
      return '';

    case 'simple':
      return "$alias.language LIKE '$current' OR $alias.language LIKE '' OR $alias.language IS NULL" ;

    case 'mixed':
      return "$alias.language LIKE '$current' OR $alias.language LIKE '$default' OR $alias.language LIKE '' OR $alias.language IS NULL" ;

    case 'strict':
      return "$alias.language LIKE '$current'" ;

    case 'node':
    case 'translation':
      return "$alias.language LIKE '". i18n_selection_mode('params') ."' OR $alias.language LIKE '' OR $alias.language IS NULL" ;

    case 'default':
      return "$alias.language LIKE '$default' OR $alias.language LIKE '' OR $alias.language IS NULL" ;

    case 'custom':
      return str_replace('%alias', $alias, i18n_selection_mode('params'));
  }
}

This change brought the query execution time down to a much more acceptable 1.3 seconds on my site. Reading around on why this might be didn't enlighten me much, since the consensus seemed to be that for simple comparisons, equals and LIKE were equivalent. However I repeatedly tested the original queries against the modified ones, and the results were 100% confirmation that on my site, performance was increased about a hundredfold by using LIKE here.

@alanpeart

did you check the patch from #26 ?

@schildi no I didn't because it looked to me like it only applied to advanced forum but maybe I was mistaken?

there is a good chance that the patch from #26 will help

I still haven't tried the patch from #26 but I just tracked down another problem query (taking up to a minute to execute a mixed mode query generated by views) and this was also brought down to just over 1 second by replacing all of the " language = " SQL clauses with " language LIKE "

The reason I haven't applied the #26 patch is that I don't fully understand the implications of changing the SQL in this way, and I don't understand why, if it works, the module maintainer hasn't applied it. Whereas in the case of the change I've made, I fully understand the implications (there aren't any, it's just much faster).

the original query - shown in the first box in #26 - contains some static parts which are evaluated for each row.
These static parts can be put into an independent (sub-)query which is evaluated only once:

(SELECT tnid FROM node WHERE tnid <> 0 AND language='de') i18n

That's where the advantage of #26 comes from.

See #1252494: Create a new module for complex query altering (mixed mode) language selection.

Schildi, I finally tried your solution - because although mine seemed to work for a while, this weekend my performance on the server totally died when traffic rose.

Changing the mixed mode query to join on a subquery as you suggested completely did the trick, and there are no implications or "complications" - it's nothing more or less than a SQL bug fix.

Just wanted to let you know since you were very patient in asking me to try it :-)

The module maintainer has made it very clear he will not be committing any changes so I will just make a mental note and keep my hacked version backed up in case of updates. I guess it's only a problem for a busy site with thousands of nodes running in "mixed mode".

Alan,

thanks for your "mental note", but most compliments I have to forward to Roderik.