Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26659

optimizer fails to consider character set conversion cost on PK joins

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.6.4
    • Fix Version/s: None
    • Component/s: Character Sets, Optimizer
    • Labels:
      None
    • Environment:
      Linux

      Description

      I isolated an amazing, catastrophic lack of performance for a common business operation. Something that with the same tables and data takes 0.5 seconds in MS SQL, in Mariadb it takes 15 minutes, and is not related to the engine, for I tried with innodb, memory and rocksdb with identical results.
      two tables

      CREATE TABLE test1 (
        callid varchar(64) NOT NULL DEFAULT '',
        created datetime NOT NULL DEFAULT current_timestamp,
        PRIMARY KEY (callid)
      )
      ENGINE = INNODB,
      AVG_ROW_LENGTH = 88,
      CHARACTER SET latin1,
      COLLATE latin1_swedish_ci;
       
      CREATE TABLE test2 (
        callid varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
        date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
        PRIMARY KEY (callid)
      )
      ENGINE = INNODB,
      AVG_ROW_LENGTH = 88,
      CHARACTER SET latin1,
      COLLATE latin1_swedish_ci;
       
      fill them with 65.000 rows (in my case)
       
      select c.* from test1 c right join test2 t on c.callid=t.callid where c.callid is null;
      +--------+---------+
      | callid | created |
      +--------+---------+
      | NULL   | NULL    |
      +--------+---------+
      1 row in set (14 min 35.123 sec)
      

      if you try to do
      select * from test2 where callid not in (select callid from test1);

      the result is the same

      This operation takes 1/2 in Ms SQL.
      What is wrong?

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            philip_38 Philip orleans
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration