Drupal 7 Performance: Blown up locales_source table

Drupal 7 Performance: Blown up locales_source table

Have you ever wondered about a performance loss on your multilanguage Drupal website? Does your Drush SQL log show you a lot of SELECT's from the 'locales_source' table?
That's not unnormal, but you might have a look at the size of your locales_source table. It could be larger than it should.

In an installation some days ago I found about 40.000 strings in "locales_source" table. I thougt that might be kind of heavy and put together the following query to find out how many strings have no translation and may be obsolete perhaps:

  1. FROM locales_source
  2. LEFT JOIN locales_target lt
  3. USING ( lid )
  4. WHERE lt.lid IS NULL

It returned about 20.000 entries without translation. I also looked for related issues and web ressources about the problem and for example found:

which convinced me, that there might be a problem.

So finally I ran the command from http://www.iws.net/node/40:

  1. DELETE locales_source FROM locales_source left join locales_target lt using (lid) where lt.lid is null;

That deleted all locale strings without translation. The ones that really exist will be rebuilt on the next page access. All others are gone. :)
And truely, a significant performance boost was measured.

So it might be interesting for you to clear the locales overhead from time to time. I'm not sure if the "Clean up left over strings" - Checkbox in i18n_string does exactly the same. That should be investigated.

Comments are welcome and perhaps my hints helped you.


Can u help me to convert that

Can u help me to convert that 2 query to the drupal query
Really helpful this blog.

You can simply use

You can simply use db_query:
db_query("DELETE locales_source FROM locales_source LEFT JOIN locales_target lt USING (lid) WHERE lt.lid IS NULL;");

Post new comment

The content of this field is kept private and will not be shown publicly.

More information about formatting options