Stop accessing wcf1_tag_to_object.languageID in TagEngine::getObjectsTags()
authorTim Düsterhus <duesterhus@woltlab.com>
Wed, 27 Jan 2021 10:44:09 +0000 (11:44 +0100)
committerMatthias Schmidt <gravatronics@live.com>
Fri, 29 Jan 2021 15:49:31 +0000 (16:49 +0100)
commite7a6cb1a5256d7adc9759b58abaa85007795b94a
treea27384fe77c1cd012a0a8e44024a6fd41138fabc
parent683280d557b76be1b49032a281386d9d9d15759b
Stop accessing wcf1_tag_to_object.languageID in TagEngine::getObjectsTags()

This stops accessing the redundant `languageID` column that is functionally
dependent on the tagID (see #3803).

This change will make the query a little bit slower, but this will be
remediated by adjusting the indices on the wcf1_tag_to_object table after which
the performance will be identical:

    MariaDB [*snip*]> EXPLAIN
        -> SELECT tag.*,
        ->        tag_to_object.objectid
        -> FROM   wcf1_tag_to_object tag_to_object
        ->        LEFT JOIN wcf1_tag tag
        ->               ON ( tag.tagid = tag_to_object.tagid )
        -> WHERE  tag_to_object.objecttypeid = 92
        ->        AND tag_to_object.objectid IN ( 3553, 7990 )
        ->        AND tag_to_object.languageid IN ( 1 );
    +------+-------------+---------------+--------+----------------------------------------------------------------------+-------------------------------------+---------+-------------------------------+------+--------------------------+
    | id   | select_type | table         | type   | possible_keys                                                        | key                                 | key_len | ref                           | rows | Extra                    |
    +------+-------------+---------------+--------+----------------------------------------------------------------------+-------------------------------------+---------+-------------------------------+------+--------------------------+
    |    1 | SIMPLE      | tag_to_object | range  | objectTypeID,objectTypeID_2,cbbba36334575d806c002a8756c8a107_fk,test | cbbba36334575d806c002a8756c8a107_fk | 12      | NULL                          |    8 | Using where; Using index |
    |    1 | SIMPLE      | tag           | eq_ref | PRIMARY                                                              | PRIMARY                             | 4       | *snip*.tag_to_object.tagID    |    1 |                          |
    +------+-------------+---------------+--------+----------------------------------------------------------------------+-------------------------------------+---------+-------------------------------+------+--------------------------+
    2 rows in set (0.00 sec)

    MariaDB [*snip*]>
    MariaDB [*snip*]> EXPLAIN
        -> SELECT tag.*,
        ->        tag_to_object.objectid
        -> FROM   wcf1_tag_to_object tag_to_object
        ->        LEFT JOIN wcf1_tag tag
        ->               ON ( tag.tagid = tag_to_object.tagid )
        -> WHERE  tag_to_object.objecttypeid = 92
        ->        AND tag_to_object.objectid IN ( 3553, 7990 )
        ->        AND tag.languageid IN ( 1 );
    +------+-------------+---------------+--------+----------------------------------------+---------+---------+-------------------------------+------+--------------------------+
    | id   | select_type | table         | type   | possible_keys                          | key     | key_len | ref                           | rows | Extra                    |
    +------+-------------+---------------+--------+----------------------------------------+---------+---------+-------------------------------+------+--------------------------+
    |    1 | SIMPLE      | tag_to_object | range  | objectTypeID,objectTypeID_2,tagID,test | test    | 8       | NULL                          |    8 | Using where; Using index |
    |    1 | SIMPLE      | tag           | eq_ref | PRIMARY,languageID                     | PRIMARY | 4       | *snip*.tag_to_object.tagID    |    1 | Using where              |
    +------+-------------+---------------+--------+----------------------------------------+---------+---------+-------------------------------+------+--------------------------+
    2 rows in set (0.00 sec)
wcfsetup/install/files/lib/system/tagging/TagEngine.class.php