Today I had a hard fight for performance of a Drupal 7 multilingual website. Using the devel.module I analyzed several MySQL InnoDB requests on each payload and their performance.
A thing that popped up again and again was the locales_source table. The translation queries were not big, but they were many. ;)
And they summed up.
Having a deeper look I found out that there was no proper index used and I googled for it. What I finally found was this issue, which sadly doesn't have much activity currently: https://drupal.org/node/1940532
As you can see, there are two main indexes missing, if you use Drupal with i18n. Especially for big or high traffic pages this is an issue!
I hope that this will be fixed soon the the modules. Until that you may be interested to create the indexes yourself:
ALTER TABLE `locales_source` ADD INDEX `locales_source_i18n_idx_tcs` ( `textgroup` , `context` , `source` ( 30 ));
ALTER TABLE `i18n_string` ADD INDEX `i18n_string_i18n_idx_tot` ( `type` , `objectid` , `textgroup` );
If that helped you, feel free to leave a comment. Our company also provides Drupal Performance optimization for your Drupal Project in Germany, Austria or Switzerland. Learn more about us: http://www.DROWL.de
