[272171] Index the string value for faster syncup
diff --git a/babel-setup.sql b/babel-setup.sql
index 08fdf35..379835c 100644
--- a/babel-setup.sql
+++ b/babel-setup.sql
@@ -228,6 +228,7 @@
CONSTRAINT `strings_ibfk_2` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE INDEX idx_value ON strings(value(40));
DELIMITER ;;
CREATE TRIGGER `upd_string` AFTER UPDATE ON `strings` FOR EACH ROW
diff --git a/classes/export/generate1.php b/classes/export/generate1.php
index b42a8f4..c021813 100755
--- a/classes/export/generate1.php
+++ b/classes/export/generate1.php
@@ -110,12 +110,14 @@
exec("mkdir ${output_dir_for_train}plugins/");
fwrite($language_pack_links_file, "\n\t<h3>Release Train: $train_id</h3>\n\t<ul>");
-
- $language_result = mysql_query("SELECT language_id, iso_code, IF(locale <> '', CONCAT(CONCAT(CONCAT(name, ' ('), locale), ')'), name) as name, is_active, IF(language_id = 1,1,0) AS sorthack FROM languages ORDER BY sorthack, name ASC");
+
+ $sql = "SELECT language_id, iso_code, IF(locale <> '', CONCAT(CONCAT(CONCAT(name, ' ('), locale), ')'), name) as name, is_active, IF(language_id = 1,1,0) AS sorthack FROM languages ORDER BY sorthack, name ASC";
+ $language_result = mysql_query($sql);
if($language_result === FALSE) {
# we may have lost the database connection.with our shell-outs
# bug 271685
$dbh = $dbc->connect();
+ $language_result = mysql_query($sql);
}
while (($language_row = mysql_fetch_assoc($language_result)) != null) {
$language_name = $language_row['name'];
diff --git a/classes/export/import_from_csv.php b/classes/export/import_from_csv.php
index 80a210e..fde7ec6 100644
--- a/classes/export/import_from_csv.php
+++ b/classes/export/import_from_csv.php
@@ -69,7 +69,7 @@
$handle = fopen($csv_file, "r");
while (($data = fgetcsv($handle)) !== FALSE) {
$sql = <<<SQL
-SELECT s.string_id FROM files AS f INNER JOIN strings AS s ON f.file_id = s.file_id INNER JOIN release_train_projects as v ON (f.project_id = v.project_id AND f.version = v.version) WHERE f.is_active AND s.non_translatable <> 1 AND s.name = '$data[0]' AND BINARY s.value = '$data[1]' AND v.train_id = '$release_train_id'
+SELECT s.string_id FROM files AS f INNER JOIN strings AS s ON f.file_id = s.file_id INNER JOIN release_train_projects as v ON (f.project_id = v.project_id AND f.version = v.version) WHERE f.is_active AND s.non_translatable <> 1 AND s.name = '$data[0]' AND s.value = BINARY '$data[1]' AND v.train_id = '$release_train_id'
SQL;
$values = mysql_query($sql);
diff --git a/classes/export/syncup.php b/classes/export/syncup.php
index 44aa55d..4823559 100644
--- a/classes/export/syncup.php
+++ b/classes/export/syncup.php
@@ -47,7 +47,7 @@
$count++;
if($count % 100 == 0) {
- echo "Processed " . $count . " strings...\n";
+ echo "Processed " . $count . " strings (language_id=$language_id)... \n";
}
$untranslated_value = $string_row['value'];
@@ -56,7 +56,8 @@
# This query split in two for added performance.
# See bug 270485
$string_ids = "";
- $rs = mysql_query( "SELECT s.string_id FROM strings AS s WHERE BINARY s.value = '" . addslashes($untranslated_value) . "'");
+ # BINARY the lookup value instead of the field to support an index
+ $rs = mysql_query( "SELECT s.string_id FROM strings AS s WHERE s.value = BINARY '" . addslashes($untranslated_value) . "'");
while ( ($row = mysql_fetch_assoc($rs)) != null) {
if(strlen($string_ids) > 0) {
$string_ids .= ",";
diff --git a/classes/string/string.class.php b/classes/string/string.class.php
index 6ba826b..d4ae683 100755
--- a/classes/string/string.class.php
+++ b/classes/string/string.class.php
@@ -84,7 +84,7 @@
FROM
strings
WHERE file_id = " . sqlSanitize($_file_id, $dbh) . "
- AND BINARY(name) = " . returnQuotedString(sqlSanitize($_name, $dbh));
+ AND name = BINARY " . returnQuotedString(sqlSanitize($_name, $dbh));
$result = mysql_query($sql, $dbh);
if($result && mysql_num_rows($result) > 0) {
diff --git a/html/query.php b/html/query.php
index 7698839..39c53da 100644
--- a/html/query.php
+++ b/html/query.php
@@ -35,7 +35,7 @@
"SELECT t.value
from strings As s inner join translations AS t on s.string_id = t.string_id
inner join languages As l on l.language_id = t.language_id
- where BINARY s.value = '" . addslashes($value) . "'
+ where s.value = BINARY '" . addslashes($value) . "'
and l.iso_code = '" . addslashes($nl) . "' ");
if ($possible_translations and (($translation_row = mysql_fetch_assoc($possible_translations)) != null)) {
