3
\$\begingroup\$

I have a table in MySQL with approx. 20 million rows and the following columns:

id | word_eng | word_indic

I have to translate the English word (the value in column word_eng) into Indian language (the value in column word_indic) using the Google Translate API.

I have written PHP code which spawns multiple cURL requests and fetches data from API and updates it into the table. But this process is quite slow; it takes about 100 to 200 words per second.

I am using RollingCurl for multiple cURL requests. I am running this as a cron job.

What's the best way to make it as fast as possible?

<?php
include_once('db.php');
include_once('functions.php');
include_once('rolling-curl-master/RollingCurl.php');

$table = $argv[1];

$q = "SELECT * from $table where word_indic is null limit 500000";
$result = $conn->query($q); $n = 0;

$urls = array();

while ($row = $result->fetch_assoc())
{
    $id = $row['id']; 
    $word = rawurlencode(getName($row['name_eng']));

//getName is a simple function which does some trimming and cleaning up of string

    $url = 'https://www.google.com/inputtools/request?text='.rawurlencode($word).'&ime=transliteration_en_te&id='.rawurlencode($id);
    array_push($urls, $url);
}
//print_r($urls);

unset($url);


$rc = new RollingCurl("request_callback");

// the window size determines how many simultaneous requests to allow.  
$rc->window_size = 300;

foreach ($urls as $url) 
{
    // add each request to the RollingCurl object
    $request = new RollingCurlRequest($url);
    $rc->add($request);
}
$rc->execute();

function request_callback($response, $info) 
{
    // parse the page title out of the returned HTML
    if (preg_match("~<title>(.*?)</title>~i", $response, $out)) {
        $title = $out[1];
    }

    //echo "<b>$title</b><br />";
    //print_r($info);
    $parts = parse_url($info['url']); 
    parse_str($parts['query'], $query);
    $id = $query['id'];
    $text = $query['text'];

    //echo "<hr>";
    $trans = json_decode($response)[1][0][1][0];    

    global $conn; global $table; global $urls; global $n;   
    if ($trans != '' and !preg_match('/[a-z]/', $trans)) 
    { 
        $conn->query("update $table set word_indic='$trans' where id='$id'"); $n++;
    }
} 

?>

Table structure:

CREATE TABLE `words` (
 `id` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `word_eng` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `word_indic` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 FULLTEXT KEY `word_eng` (`word_eng`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
\$\endgroup\$
  • \$\begingroup\$ I am sorry for being too quick editing your question. Mysql could be indeed a bottleneck. if your table is innodb, then wrapping a batch in a transaction could greatly improve the performance on the mysql side \$\endgroup\$ – Your Common Sense Dec 14 '17 at 8:01
  • \$\begingroup\$ Did you track/measure the performance of the different parts of your script? Do you know, whether Google Input Tools have a restriction for requests per second? You may send more requests and get faster results by using Google's Cloud Translation API. Unfortunately this API isn't free. \$\endgroup\$ – insertusernamehere Dec 14 '17 at 9:19
  • \$\begingroup\$ @YourCommonSense Yes my table is innodb and I have set innodb_flush_log_at_trx_commit = 0 I had put fulltext index on word_eng but not on word_indic. id is varchar and primary key. Can you tell me if proper indexing can improve performance? \$\endgroup\$ – Naveed Dec 14 '17 at 10:19
  • 1
    \$\begingroup\$ @insertusernamehere Cloud translation API 'translates' the word, what I need is transliteration for which google input tools is a work around and its pretty fast. Not sure how to check if it has per limit request restriction. \$\endgroup\$ – Naveed Dec 14 '17 at 10:21
  • \$\begingroup\$ what is varchar size and encoding? 255 with utf8 will make something 760 bytes \$\endgroup\$ – Your Common Sense Dec 14 '17 at 10:31
3
\$\begingroup\$

Url building

$id = $row['id']; 
$word = rawurlencode(getName($row['name_eng']));
//getName is a simple function which does some trimming and cleaning up of string
$url = 'https://www.google.com/inputtools/request?text='.rawurlencode($word).'&ime=transliteration_en_te&id='.rawurlencode($id);

Could be simplified with the use of http_build_query() with encoding type PHP_QUERY_RFC3986 to replace the rawurlencode:

$row['text'] = getName($row['name_eng']);
//unset $row['name_eng'] or select it as "text" in the SELECT statement
$row['ime'] = 'transliteration_en_te'; //or move into SELECT fields in query?
$url = 'https://www.google.com/inputtools/request?'.http_build_query($row, '', '&', PHP_QUERY_RFC3986);

Obviously that will likely result in extra parameters tacked onto the URL (e.g. word_indic, word_eng). You could update the select query to specifically select those fields:

$q = "SELECT id, name_eng as text from $table where word_indic is null limit 500000";

And you could select that ime parameter as a hard-coded value, so as to not need to assign it to each row in the while, but then that would make the result set larger...

Global variables

I see this line:

global $conn; global $table; global $urls; global $n;  

In general it isn't a good habit to be referencing variables globally. Obviously this is in the context of a callback function but there are ways to avoid that. I would suggest creating a wrapper class and storing the connection, table and other info in member/instance variables while calling the method using the array syntax.

The Singleton Pattern could be used to create a wrapper class on the database code and call a static method to get a connection to the database, instead of referencing $conn globally.

Also, $urls and $n don't appear to be used after that line anyway...

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.