vb.org Archive
Page 29 of 70 « First 192728 29 303139 Last »
Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   Modification Graveyard (https://vborg.vbsupport.ru/forumdisplay.php?f=224)
-   -   Miscellaneous Hacks - vB Global Translator - Multiply your indexed pages & put search traffic on autopilot (https://vborg.vbsupport.ru/showthread.php?t=217329)

NLP-er 07-07-2009 12:09 PM
Quote:
Originally Posted by Dave Hybrid (Post 1844425)
To combat them I set a new email in vb config and an outlook rule and now they all go to their own folder in outlook. No big deal.
What do you think Dave to write about it in mod description as known issues instead of telling new people about it over and over again... :) I saw question about this issue so many times... Also in PM.

NLP-er 07-07-2009 01:20 PM
Was asking about miracle - I give you miracle. I changed one query in deletion of duplicated data and now it is very fast. I will just tell that before changes 1 query on 100 000 rows with 1000 duplicated rows took 10 minutes before update, now it takes less than 1 second!!! :D

So cleaning each table has 5 queries. 2 are instant, 2 are very fast and one took me 7 seconds on 100 000 rows - this one cannot be optimized, there is no any subquery, we just need to ask DB about those data.

Hope now Dave will made new official release, which will not allow for data duplication in 2 of our 3 cache tables, and makes whole mod works faster, and your DB smaller.

Below you have again full and updated description how to clean duplicated data. I was able to run this by my browser client, but be aware, that in case of some large databases, server can go away by this client and in such case you will need to use some other client than www.

Also note that if you made changes described here (https://vborg.vbsupport.ru/showpost....&postcount=242) which I hope will be included in official release, then you will not have to remove duplicated data from wt_cache_short and wt_cache_medium anymore (only once during described procedure). And after that only wt_cache will need to delete duplicated data from time to time.

So here you have again description and procedure, but much faster this time :):
If you would like to check do you have data duplication in your cache execute those queries (time consuming). Execute one by one - each one works on other cache table and tells you how many times and which data is duplicated (1st column duplication counter, 2nd for which originaltext, 3rd for which language):
Code:
select count(*) counter, originaltext, tl from wt_cache group by originaltext, tl having count(*) > 1 order by counter desc;

select count(*) counter, originaltext, tl from wt_cache_short group by originaltext, tl having count(*) > 1 order by counter desc;

select count(*) counter, originaltext, tl from wt_cache_medium group by originaltext, tl having count(*) > 1 order by counter desc;
If you want to delete data duplication first need to create 2 tables for temporary data:
Code:
CREATE TABLE saver (
id INT,
tl VARCHAR(10),
originaltext VARCHAR(65000)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE cleaner (
id INT
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;
And when you have those you can execute clearing queries - note those will leave first translation in your database and only remove next translations for same text and language.
Code:
delete from cleaner;
delete from saver;
insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache group by originaltext,tl having count(*) > 1);
insert into cleaner (SELECT cache.id from saver, wt_cache cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id);
DELETE FROM wt_cache USING wt_cache INNER JOIN cleaner ON wt_cache.id = cleaner.id;

delete from cleaner;
delete from saver;
insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache_short group by originaltext,tl having count(*) > 1);
insert into cleaner (SELECT cache.id from saver, wt_cache_short cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id);
DELETE FROM wt_cache_short USING wt_cache_short INNER JOIN cleaner ON wt_cache_short.id = cleaner.id;

delete from cleaner;
delete from saver;
insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache_medium group by originaltext,tl having count(*) > 1);
insert into cleaner (SELECT cache.id from saver, wt_cache_medium cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id);
DELETE FROM wt_cache_medium USING wt_cache_medium INNER JOIN cleaner ON wt_cache_medium.id = cleaner.id;

racale 07-07-2009 01:33 PM
This product works with version 3.8.3
I tried but it does not work
thanks



Code:

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;

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;


https://vborg.vbsupport.ru/external/2009/07/31.gif

NLP-er 07-07-2009 01:43 PM
Quote:
Originally Posted by racale (Post 1844487)
This product works with version 3.8.3
I tried but it does not work
thanks



Code:

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;

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;


https://vborg.vbsupport.ru/external/2009/07/31.gif
You already created table wt_cache :) If you want to update - just fallow update procedure, or drop table first (will lose all data if have any).

racale 07-07-2009 02:08 PM
Quote:
Originally Posted by NLP-er (Post 1844492)
You already created table wt_cache :) If you want to update - just fallow update procedure, or drop table first (will lose all data if have any).
First of all I wanted to say that nn are very experienced

I'm running the full instructions ......
just that when I send Code: mida that error
I cancel the DB and tried to do anything but

I do not know how to do

https://vborg.vbsupport.ru/external/2009/07/29.gif

https://vborg.vbsupport.ru/external/2009/07/30.gif

NLP-er 07-07-2009 03:17 PM
Quote:
Originally Posted by racale (Post 1844508)
First of all I wanted to say that nn are very experienced

I'm running the full instructions ......
just that when I send Code: mida that error
I cancel the DB and tried to do anything but

I do not know how to do

https://vborg.vbsupport.ru/external/2009/07/29.gif

https://vborg.vbsupport.ru/external/2009/07/30.gif
You aready have DB set. So what is the problem?

Dave Hybrid 07-07-2009 04:03 PM
Quote:
Originally Posted by NLP-er (Post 1844479)
Was asking about miracle - I give you miracle. I changed one query in deletion of duplicated data and now it is very fast. I will just tell that before changes 1 query on 100 000 rows with 1000 duplicated rows took 10 minutes before update, now it takes less than 1 second!!! :D

So cleaning each table has 5 queries. 2 are instant, 2 are very fast and one took me 7 seconds on 100 000 rows - this one cannot be optimized, there is no any subquery, we just need to ask DB about those data.

Hope now Dave will made new official release, which will not allow for data duplication in 2 of our 3 cache tables, and makes whole mod works faster, and your DB smaller.

Below you have again full and updated description how to clean duplicated data. I was able to run this by my browser client, but be aware, that in case of some large databases, server can go away by this client and in such case you will need to use some other client than www.

Also note that if you made changes described here (https://vborg.vbsupport.ru/showpost....&postcount=242) which I hope will be included in official release, then you will not have to remove duplicated data from wt_cache_short and wt_cache_medium anymore (only once during described procedure). And after that only wt_cache will need to delete duplicated data from time to time.

So here you have again description and procedure, but much faster this time :):
If you would like to check do you have data duplication in your cache execute those queries (time consuming). Execute one by one - each one works on other cache table and tells you how many times and which data is duplicated (1st column duplication counter, 2nd for which originaltext, 3rd for which language):
Code:
select count(*) counter, originaltext, tl from wt_cache group by originaltext, tl having count(*) > 1 order by counter desc;

select count(*) counter, originaltext, tl from wt_cache_short group by originaltext, tl having count(*) > 1 order by counter desc;

select count(*) counter, originaltext, tl from wt_cache_medium group by originaltext, tl having count(*) > 1 order by counter desc;
If you want to delete data duplication first need to create 2 tables for temporary data:
Code:
CREATE TABLE saver (
id INT,
tl VARCHAR(10),
originaltext VARCHAR(65000)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE cleaner (
id INT
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;
And when you have those you can execute clearing queries - note those will leave first translation in your database and only remove next translations for same text and language.
Code:
delete from cleaner;
delete from saver;
insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache group by originaltext,tl having count(*) > 1);
insert into cleaner (SELECT cache.id from saver, wt_cache cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id);
DELETE FROM wt_cache USING wt_cache INNER JOIN cleaner ON wt_cache.id = cleaner.id;

delete from cleaner;
delete from saver;
insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache_short group by originaltext,tl having count(*) > 1);
insert into cleaner (SELECT cache.id from saver, wt_cache_short cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id);
DELETE FROM wt_cache_short USING wt_cache_short INNER JOIN cleaner ON wt_cache_short.id = cleaner.id;

delete from cleaner;
delete from saver;
insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache_medium group by originaltext,tl having count(*) > 1);
insert into cleaner (SELECT cache.id from saver, wt_cache_medium cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id);
DELETE FROM wt_cache_medium USING wt_cache_medium INNER JOIN cleaner ON wt_cache_medium.id = cleaner.id;
That worked much better, will test and release shortly. Are you planning any more optimization? Or are we 100%? :up:

LoveStream 07-07-2009 04:09 PM
Hello.
Thanks for your hack.

This is my first trial, but I encount below error.

Code:
Fatal error: Call to undefined function: mysql_set_charset() in /home/hosting_users/www/forum/translate.php on line 35
My target goal is that;

our origin text is mainly Korean
other translate to all other languages.

Above line 35 is,

Code:
////////////////////////////////////////////////////
if ($enablecache) {
mysql_connect ($mysqlserver, $dbusername, $dbpassword);
mysql_select_db ($dbname);
#35: mysql_set_charset('utf8');
}
////////////////////////////////////////////////////
Though, our main lanuage is Korean, but I did not change it to "ko".

PHP Code:
<?php
////////////////////////////////////////////////////
// Global Translator API
////////////////////////////////////////////////////

global $enablesession, $enablecache, $originalEncoding, $fl;
////////////////////////////////////////////////////
//        SETTINGS
////////////////////////////////////////////////////
$enablesession = false; //ignore
$enablecache = true; //true - enable cache setting, false - disable cache setting
$originalEncoding = 'iso-8859-1'; - //refer to your forum source code for your base encoding
$fl = 'en'; //current language (original) - refer table for language varibles
I had tried this $originalEncoding = 'iso-8859-1'; to $originalEncoding = 'utf-8';, but it doesn't work with the same error.

My Server set is,
  • PHP 4.4.1
  • MySQL 5.0.19
  • cURL: enable
  • mysql character set : utf-8

What it's problem?
help me. Thank you.

1Unreal 07-07-2009 04:52 PM
Thanks for the updates :)

1Unreal 07-07-2009 04:57 PM
Quote:
Originally Posted by 1Unreal (Post 1843565)
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.
Bumping my post.

I think this would be a very valuable feature


All times are GMT. The time now is 07:59 PM.
Page 29 of 70 « First 192728 29 303139 Last »
Show 40 post(s) from this thread on one page

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.02151 seconds
  • Memory Usage 1,804KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (8)bbcode_code_printable
  • (1)bbcode_php_printable
  • (6)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (4)pagenav_pagelink
  • (2)pagenav_pagelinkrel
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (10)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • pagenav_page
  • pagenav_complete
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete