MySQL enhancements / slow queries

  • Resolved mareksebera

    (@mareksebera)


    1 year, 11 months ago

    Hi, after troubleshooting our installation (wordpress 6.1,
    TranslatePress – Multilingual 2.4.2), i found out that gettext mysql tables are not-optimal and some slow queries, resolving these issues in source code and database manually solved our issues (slow rendering of each page)

    So for anyone, and maybe developers of translate-press even, here’s what i did:

    (1) missing indexes
    eg. wp433_trp_gettext_en_gb missing index/key on columns “domain”, “original_id” (and maybe “status”)
    -> manually via phpmyadmin, in each gettext table structure, add “index” to at least those 2 columns

    original create table query

    CREATE TABLE wp433_trp_gettext_en_gb (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    original longtext NOT NULL,
    translated longtext DEFAULT NULL,
    domain longtext DEFAULT NULL,
    status int(20) DEFAULT NULL,
    original_id bigint(20) DEFAULT NULL,
    plural_form int(20) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY id (id),
    KEY index_name (original(100)),
    KEY domain (domain(768)),
    KEY status (status),
    KEY original_id (original_id),
    FULLTEXT KEY original_fulltext (original)
    ) ENGINE=InnoDB AUTO_INCREMENT=24464 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`

    updated create table

    CREATE TABLEwp433_trp_gettext_en_gb` (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    original longtext NOT NULL,
    translated longtext DEFAULT NULL,
    domain longtext DEFAULT NULL,
    status int(20) DEFAULT NULL,
    original_id bigint(20) DEFAULT NULL,
    plural_form int(20) DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY id (id),
    KEY index_name (original(100)),
    // => these keys are added
    KEY domain (domain(768)),
    KEY status (status),
    KEY original_id (original_id),
    // => end of modifications
    FULLTEXT KEY original_fulltext (original)
    ) ENGINE=InnoDB AUTO_INCREMENT=24464 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`

    (2) slow query involving union/distinct/collate

    method: translatepress-multilingual/includes/string-translation/class-string-translation.php::TRP_String_Translation::get_gettext_domains

    default constructed query looks like this:
    SELECT domain FROM wp433_trp_gettext_en_gb UNION SELECT domain FROM wp433_trp_gettext_en_us UNION SELECT domain FROM wp433_trp_gettext_cs_cz ORDER BY domain ASC;

    this uses UNION(DISTINCT) and ORDER and takes about 1.7 seconds on MariaDB 10.5.18

    -> manually updated function to use “UNION ALL” and not add “order by”, this took the query down to 0.075 second

Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter mareksebera

    (@mareksebera)

    Also to point out, the slow queries I identified using free wp plugin “Query Monitor”, https://querymonitor.com/

    I am experiencing the same problem where after updating to 2.3.8, the gettext queries went from taking 5ms to over 20ms for each request. That is a huge jump in throughput and processing power from the database. We are running this plugin on a very large, mission-critical site and this is affecting the load time for every page request.

    @mareksebera Thank you for the fix. Can you paste your updated SQL query?

    TranslatePress folks. Are you able to change how the database table is created and update the SQL query to be more efficient as shown above? Are you currently working on a fix for this?

    Thread Starter mareksebera

    (@mareksebera)

    @claytoncollie i am unable to, specific query depends on your wp/tp settings, the query asks for unique gettext domain names across tables for all the currently enabled languages. Underlying tables did not have INDEX on “domain” columns. Query of several joined “UNION DISTINCT” (default one) took wild minutes. All the bad decisions here.

    Optimally the function should be refactored and ask for distinct values in index of “domain” column (even several separate selects, with results later joined in-memory, are faster for large tables (indexes) than any “UNION DISTINCT” one)

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘MySQL enhancements / slow queries’ is closed to new replies.