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

Optimizer bug when making INNER JOIN with Foreign Keys

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4.7
    • 10.4
    • Optimizer
    • Debian 9.2, Core i7-7700k, 32G RAM

    Description

      It seems that optimizer fails when executing INNER JOIN select when tables are related by a foreign key.

      There are 3 tables:

      CREATE TABLE `default_work` (
        `add_date` datetime(6) NOT NULL,
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `title` varchar(255) NOT NULL,
        `keywords` varchar(255) DEFAULT NULL,
        `short_text` longtext DEFAULT NULL,
        `downloads` int(10) unsigned NOT NULL,
        `published` tinyint(1) NOT NULL,
        `subject_id` int(11) NOT NULL,
        `work_type_id` int(11) NOT NULL,
        PRIMARY KEY (`id`),
        KEY `default_work_subject_id_IDX` (`subject_id`) USING BTREE,
        KEY `default_work_work_type_id_IDX` (`work_type_id`) USING BTREE,
        CONSTRAINT `default_work_FK` FOREIGN KEY (`subject_id`) REFERENCES `default_subject` (`id`),
        CONSTRAINT `default_work_FK_1` FOREIGN KEY (`work_type_id`) REFERENCES `default_worktype` (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=210673 DEFAULT CHARSET=utf8
       
      CREATE TABLE `default_subject` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `subject` varchar(255) NOT NULL,
        `old_id` int(10) unsigned NOT NULL,
        `subject_literal` varchar(255) NOT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8
       
      CREATE TABLE `default_worktype` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `work_type` varchar(250) NOT NULL,
        `description` longtext DEFAULT NULL,
        `old_id` int(10) unsigned NOT NULL,
        `work_type_literal` varchar(250) NOT NULL,
        `title` varchar(255) NOT NULL,
        `multiple` varchar(255) NOT NULL,
        `keywords` varchar(255) NOT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `default_worktype_old_id_a8b508fe_uniq` (`old_id`),
        UNIQUE KEY `default_worktype_work_type_literal_1e609434_uniq` (`work_type_literal`)
      ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
      

      The default_work has about 200,000 records, default_subject - 42, and default_worktype - 12

      When executing this query:

      SELECT `default_work`.`id`, `default_work`.`title`, `default_worktype`.`work_type`,`default_subject`.`subject`
      FROM `default_work`
      INNER JOIN `default_subject` ON (`default_work`.`subject_id` = `default_subject`.`id`)
      INNER JOIN `default_worktype` ON (`default_work`.`work_type_id` = `default_worktype`.`id`) 
      ORDER BY `default_work`.`id` DESC LIMIT 100
      

      I got about 9 seconds of execution time, with explain says what no primary key is used. But when I deleted foreign keys and appropriate indexes - the query runs fast and smooth. (Both explain results shown in attached images).

      Attachments

        Activity

          People

            igor Igor Babaev
            exp131 Vitaly Radchik
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.