Miscellaneous Hacks - vB Global Translator - Multiply your indexed pages & put search traffic on autopilot

**Text Removed**

[Geraldm]

Originally Posted by Dave Hybrid

I managed to find your old PM, it looks fine to me mate.

Ahh it was only translating the keywords and not the description ...... I re-read your instructions and noticed I missed the bit about changing the execution order I've now changed it to 50 and yes it's now translating the keywords and desc.... Great stuff!

Thank you!

[Dave Hybrid]

Great, you're welcome. :up:

[GoTTi]

Originally Posted by NLP-er

You can always set $enablecache = false; in translate.php - then you will know does your page translates at all. I'm quite sure that cache is not an issue here and we can be sure by temporary disabling it.

its doing the same thing. i put false on the .php and reuploaded, reloaded the pages, and clicked a flag and all its doing is loading, then it stops.

[GoTTi]

i just installed a testboard, clean, no mods on it and only installed this mod here, made a post, clicked a flag and nothing is happening. what could possibly be causing this to not work?

[NLP-er]

Since some installations still have problems using cache and we are working on that, for those who has running vb global translator I have some optimizations

1. Query optimization - change in template.php code between /* Check cache for translation */ and /* -- if not found, proceed with Google Translate */ to:

/* Check cache for translation */
if ($enablecache) {
  $sql=null;
  $length = strlen($text);
  if ($length<=50) {
    $sql = mysql_query("SELECT translated FROM wt_cache_short WHERE originaltext='".addslashes($text)."' AND tl='".addslashes($tl)."' LIMIT 1");  
  } else if ($length > 255) {
    $sql = mysql_query("SELECT translated FROM wt_cache WHERE originaltext='".addslashes($text)."' AND tl='".addslashes($tl)."' LIMIT 1");
  } else {
    $sql = mysql_query("SELECT translated FROM wt_cache_medium WHERE originaltext='".addslashes($text)."' AND tl='".addslashes($tl)."' LIMIT 1"); 
  }
  
	while($t = mysql_fetch_array($sql)) {
		return $lsto.$t['translated'].$rsto;
	}
}
/* -- if not found, proceed with Google Translate */

Unnecessary data was taken from DB. Hope Dave will make new release quickly and include this change

2. Index changes. Since for someone DB was growing fast I made some tests. It is enough to change indexes.

For installed products run those queries:

alter table wt_cache_short drop index tl;
create index originaltext on wt_cache_short (originaltext, tl);

alter table wt_cache_medium drop index tl;
create index originaltext on wt_cache_medium (originaltext, tl);

alter table wt_cache drop index tl;
create index originaltext on wt_cache (originaltext(50), tl);

For new installations it will be:

CREATE TABLE wt_cache (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
tl VARCHAR(10),
originaltext VARCHAR(65000),
translated TEXT,
INDEX (originaltext (50), tl)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE wt_cache_medium (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
tl VARCHAR(10),
originaltext VARCHAR(255),
translated VARCHAR(1000),
INDEX (originaltext, tl)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE wt_cache_short (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
tl VARCHAR(10),
originaltext VARCHAR(50),
translated VARCHAR(255),
INDEX (originaltext, tl)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;

Hope Dave will change installation manual to this one.

I realized that column tl has only 28 possible values (or less if you don’t use all flags) and that is not too good for indexes. Only by changing order from (tl, originaltext) to (originaltext, tl) my database size changed from 51MB to 44,3MB without any performance lost and even with improvement. Changing it only to (originaltext) have worst results that (originaltext, tl).

Here are some results of my tests (I have weak internet connection so don't look on times but on differences between performance in different configurations):
WITH index (tl,originaltext) DB size=51MB TESTS RESULTS:
MIN: 4484 MAX: 13266 AVG: 7823
MIN: 3188 MAX: 9703 AVG: 5848
MIN: 3797 MAX: 9594 AVG: 5683
TOTAL AVG: 6451

WITH index (originaltext,tl) DB size=44,3MB TESTS RESULTS:
MIN: 2859 MAX: 10172 AVG: 6232
MIN: 2890 MAX: 9094 AVG: 6109
MIN: 3468 MAX: 9500 AVG: 5813
TOTAL AVG: 6051

WITH index (originaltext) DB size=43,2MB TESTS RESULTS:
MIN: 4454 MAX: 10406 AVG: 5998
MIN: 3703 MAX: 13922 AVG: 6341
MIN: 2922 MAX: 14578 AVG: 6888
TOTAL AVG: 6409

Times are in ms. In each test 20 different cached translated pages was generated. For each setting 3 tests series at row was executed.

[NLP-er]

For those who have problems with using cache at all - right now I suspect that there is problem with making connection to DB. Even when all settings are ok - It seems that script is waiting for connection and DB holds it. Is someone with this problem has a clue what to change (maybe some number of connections limit) It would be good if share solution with others. As long as I'm not able to reproduce this on my side, or someone will give me access to his forum, I'm not even sure does this is a problem - and it is only a suspicion.

[NLP-er]

Originally Posted by GoTTi

i just installed a testboard, clean, no mods on it and only installed this mod here, made a post, clicked a flag and nothing is happening. what could possibly be causing this to not work?

If it is clean, I think you could PM me access data to your adminCP and FTP. Then I can check what is going on Right now I can only tell that somesthing wrong

[alqloob alsahya]

nice

Excellent subject of very beautiful

Thank you

[GoTTi]

my site is blocked from google adsense, would that matter in this situation?

[GoTTi]

Originally Posted by NLP-er

If it is clean, I think you could PM me access data to your adminCP and FTP. Then I can check what is going on Right now I can only tell that somesthing wrong

ive sent u a PM with the login.