Closed Bug 473000 Opened 11 years ago Closed 9 years ago

Free tags translation page cannot be loaded

Categories

(support.mozilla.org :: Localization, task)

task
Not set

Tracking

(Not tracked)

RESOLVED INCOMPLETE

People

(Reporter: toniher, Assigned: paulc)

References

()

Details

(Keywords: perf)

Attachments

(1 file)

I formerly used:
http://support.mozilla.com/tiki-freetag_translate.php

for translating existing free tags into other languages, so they can have their own correspondences.

It seems there are too many tags in the list and page cannot be loaded? I get a "network reset error".

There is no problem when trying to translate tags associated to one page, for instance:
http://support.mozilla.com/tiki-freetag_translate.php?locale=ca&objId=Paraules+clau+intel%C2%B7ligents

Having the whole list again would be quite useful for improving tagging in different languages.
I can confirm that these pages aren't accessible. Chris, can you confirm this and advice? Thanks!
No advice. CCing Nelson and Laura.
Assignee: nobody → paul.craciunoiu
Keywords: perf
Target Milestone: --- → 1.3
Attached patch patch v1Splinter Review
Simple patch, assign_by_ref instead of assign.
Attachment #384150 - Flags: review?(smirkingsisyphus)
Attachment #384150 - Flags: review?(laura)
Target Milestone: 1.3 → 1.2.1
Comment on attachment 384150 [details] [diff] [review]
patch v1

This is still timing out for me, even with the patch...
Attachment #384150 - Flags: review?(laura) → review-
How important is this page? We could spend time to optimise the query or just have it load in a reasonable time.
Target Milestone: 1.2.1 → 1.3
current query:

SELECT DISTINCT fo.tagId tagset, (SELECT lang FROM tiki_freetags WHERE tagId = tagset) rootlang, tag.tagId, tag.lang, tag.tag FROM tiki_objects o INNER JOIN tiki_freetagged_objects fo ON o.objectId = fo.objectId LEFT JOIN tiki_translated_objects to_a ON to_a.type = 'freetag' AND to_a.objId = fo.tagId LEFT JOIN tiki_translated_objects to_b ON to_b.type = 'freetag' AND to_a.traId = to_b.traId LEFT JOIN tiki_freetags tag ON to_b.objId = tag.tagId OR tag.tagId = fo.tagId WHERE o.type = "wiki page" AND (tag.lang IS NULL OR 1 ) AND tag.tagId IS NOT NULL;

returns 19537 rows in set (13 min 29.51 sec)

[note the or 1 is actually a huge list of locales but which is always true if you search for a valid locale, by default]

very first pass optimization (adding two where clauses to take advantage of an index):

SELECT DISTINCT fo.tagId tagset, (SELECT lang FROM tiki_freetags WHERE tagId = tagset) rootlang, tag.tagId, tag.lang, tag.tag FROM tiki_objects o INNER JOIN tiki_freetagged_objects fo ON o.objectId = fo.objectId LEFT JOIN tiki_translated_objects to_a ON to_a.type = 'freetag' AND to_a.objId = fo.tagId LEFT JOIN tiki_translated_objects to_b ON to_b.type = 'freetag' AND to_a.traId = to_b.traId LEFT JOIN tiki_freetags tag ON to_b.objId = tag.tagId OR tag.tagId = fo.tagId WHERE o.type = "wiki page" AND (tag.lang IS NULL OR 1 ) AND tag.tagId IS NOT NULL AND to_a.type = 'freetag' AND to_b.type = 'freetag'; 

15590 rows in set (7 min 45.31 sec) -- almost a 50% drop!

The real problem is we're doing a left join between a 7713-row table, a 17303-row table, a 26861-row table and 7469-row table (twice!).  There are indexes but it's still a massive massive table to sort through.
Here are some benchmarks on this:
The query statement selecting COUNT(*) with NO "WHERE" clause takes 325 seconds (5m41s) and returns 178714 rows, while joining 4 giant tables - one of them twice.

I tried limiting the results to 10 per page, taking out the "DISTINCT", taking out filtering by type or language - pretty much nothing reduces the load below 1m. As you can see, the join itself takes way long, even with an empty WHERE clause.

A more similar query can be found below. The count for the following query is 44368. I don't see any possible way to have search working on this, unless we cache stuff in a secondary table -- that might be the only solution? We could have a script populating it every so often I guess...

SELECT COUNT(*)
FROM
  tiki_objects o
  JOIN tiki_freetagged_objects fo ON o.objectId = fo.objectId
  LEFT JOIN tiki_translated_objects to_a ON to_a.type = 'freetag' AND to_a.objId = fo.tagId
  LEFT JOIN tiki_translated_objects to_b ON to_b.type = 'freetag' AND to_a.traId = to_b.traId
  LEFT JOIN tiki_freetags tag ON to_b.objId = tag.tagId OR tag.tagId = fo.tagId
WHERE
  o.type = 'wiki page'
  AND (tag.lang IS NULL OR tag.lang = 'en')
  AND tag.tagId IS NOT NULL


As I said before, I'm fairly stuck on this. Relating the query to the functionality of the page, this is what the page should be doing:
Get list of tags by language, and their translations. E.g selecting English + French shows you equivalent translated tags in the two languages (one tag being a translation of the other).
This is useful because it allows assigning languages to freetags and translating freetags.

What to do?

Since there are so many tags, it makes no sense to list them all. We should, instead, allow searching. Searching by object loads well enough (as comment 0 points out), but searching by tag name takes well over a minute (even for english only. adding other languages more than triples that time).
I can't imagine this page being very useful without searching tags, so the only solution I can think of is to only allow searching by objects, and no listing unless that happens. An even better solution is to not use it :D
Definitely not making 1.3.
Target Milestone: 1.3 → ---
Comment on attachment 384150 [details] [diff] [review]
patch v1

Just going through my r? list
Attachment #384150 - Flags: review?(smirkingsisyphus)
Code is gone.
Status: NEW → RESOLVED
Closed: 9 years ago
Resolution: --- → INCOMPLETE
You need to log in before you can comment on or make changes to this bug.