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

**Text Removed**

[Dave Hybrid]

So you suggest I now release 2 versions, one for new and one for people who want to keep their old cache. I really don't think it's that much of a big deal. I appreciate your work but I think I speak for most when I say i want to keep my 1gig database that took time to grow. I cannot maintain two releases.

[Dave Hybrid]

FYI my host tried with a Mysql desktop client and it stopped on delete from cleaner.

[NLP-er]

Hello one little DB update, after which DB will be faster.
(Index will be larger, but also faster - more unique records in index)

Note that this update is independent of last one, so hope Dave will include it in official release even if the last one will wait a little.

Whole change is about index size for originaltext - it was changed from 50 to 323 (max according to mysql limitations).

For new installations - code for wt_cache is different now:

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

For update just execute:

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

As I wrote it is faster. Here you have results of test before update:
MIN: 3141 MAX: 9109 AVG: 5389
MIN: 3297 MAX: 24891 AVG: 5252
MIN: 2828 MAX: 7500 AVG: 4296

MIN: 2609 MAX: 25594 AVG: 5934
MIN: 3438 MAX: 7890 AVG: 4782
MIN: 3578 MAX: 5485 AVG: 4196
TOTAL AVG: 4974

And here after update:
MIN: 2813 MAX: 28016 AVG: 5785
MIN: 2813 MAX: 5187 AVG: 4021
MIN: 3016 MAX: 5109 AVG: 4159

MIN: 2953 MAX: 6750 AVG: 4228
MIN: 2735 MAX: 7938 AVG: 4109
MIN: 3359 MAX: 7125 AVG: 4607
TOTAL AVG: 4484

Each test was generating 20 translated pages in each series. There was 3 series for each test. Each test was executed twice. Times are in ms.

Note that there is still place for some little improvements - like column tl uses max 5 signs and is set for 10... Also it is enough for tl to set encoding 'iso-8859-1' instead of utf8, so it will reserve only 5 bytes in index instead 30 like now, and index for originaltext will be longer to something like 331 letters (now upgraded have 323). But it shouldn’t give any significant speed improvements. So I leave this topic.

[NLP-er]

Originally Posted by Dave Hybrid

FYI my host tried with a Mysql desktop client and it stopped on delete from cleaner.

Delete from cleaner should be immediate. Are you sure you established connection to DB? Can you execute any query?
Did you try execute just delete from cleaner - without other queries? Does your client needs semicolon at the end of query? Make sure you executed delete from cleaner; with semicolon. It is possible that when you did it without semicolon server still waits for rest of instruction

[NLP-er]

Originally Posted by Dave Hybrid

So you suggest I now release 2 versions, one for new and one for people who want to keep their old cache. I really don't think it's that much of a big deal. I appreciate your work but I think I speak for most when I say i want to keep my 1gig database that took time to grow. I cannot maintain two releases.

No. I was suggesting to release new version, and those who wants can upgrade, those who wants can stay with old one, and new members will start form the new one - just like it happens with every update in every mod Was just telling you to test it on clear instance, so you will be sure that what you releasing works.

Your mod - your releases - your decisions. I gave update instrucions so it is possible to update without any data loss, and of course it is time consuming when you have such big database. I'm going right now on updated version

[1Unreal]

Would you be able to create something which will detect the users language. You can get it from $_SERVER['HTTP_ACCEPT_LANGUAGE']. It gives a list of their accepted languages.

[Dave Hybrid]

v2.3b

* Small change to database optimization

To upgrade;

Run this MySQL query.

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

[Sweeks]

Thanks for the update Dont know if I see a speed increase but it's functioning just fine

[Dave Hybrid]

Thanks for letting me know sweeks! :up:

[Geraldm]

Ok I'm a bit confused with all the different updates throughout this thread .... I'm still using v2.0 of the script. Can you please tell me the easiest way to upgrade to v2.3b while preserving the translations already in the DB?

Thanks,
Gerald.