[NLP-er]

I notice that for some data cache is not used, translation is made again and again cache is populated. So there can be data duplication in DB. It is rare and didn't find out why yet (1 clue - working on that).

If you would like to check do you have data duplication in your cache execute those queries. 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):

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;

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

If someone will find out some rule in those duplicated data it would be great


So till it will be solved you can just clear data duplications. I can share my queries to do that, but be aware that those can evaluating for some time and I had to connect by some mysql client on my comp, because by www client server was passing away before query finished it's job.

So if you want to delete data duplication first need to create 2 tables for temporary data:

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 quereies - note those will leave first translation in your database and only remove next translatioss for same text and language:

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 id from wt_cache_short where (originaltext, tl) in (SELECT originaltext, tl from saver) and id not in (SELECT id from saver));
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 id from wt_cache_medium where (originaltext, tl) in (SELECT originaltext, tl from saver) and id not in (SELECT id from saver));
DELETE FROM wt_cache_medium USING wt_cache_medium INNER JOIN cleaner ON wt_cache_medium.id = cleaner.id;

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 id from wt_cache where (originaltext, tl) in (SELECT originaltext, tl from saver) and id not in (SELECT id from saver));
DELETE FROM wt_cache USING wt_cache INNER JOIN cleaner ON wt_cache.id = cleaner.id;