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

Performance issues for select queries with wrong data type

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.1, 10.2, 10.3, 10.4
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      Debian 9, MariaDB 10.1.26 (and higher)

      Description

      After upgrading to version 10.1.26 from 10.0.32 we have performance issues caused by select queries where we use strings as parameters instead of the correct data type ints. In earlier versions MariaDB/MySQL handled the wrong strings as if it were ints and executed the queries in the same manner. If we change the parameters to ints instead of strings in 10.1.26 the queries executes as in 10.0.32.

      In our case the issue causes I/O-problems due to creation of tmp disk tables (Created_tmp_disk_tables increments by 1 every time the query is executed). The problem has been reproduced in newer versions of MariaDB as well.

      Tables/data to reproduce the problem:

      CREATE TABLE `a` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `b_id` int(11) unsigned NOT NULL DEFAULT '0',
        `c_id` varchar(25) NOT NULL DEFAULT '',
        `value` text,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
       
      CREATE TABLE `b` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
       
      CREATE TABLE `c` (
        `id` varchar(25) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
       
      INSERT INTO `a` (`id`, `b_id`, `c_id`, `value`) VALUES (1, 1, '1', 'test');
      INSERT INTO `b` (`id`) VALUES (1);
      INSERT INTO `c` (`id`) VALUES ('1');
      

      Query to reproduce the problem (notice string for a.b_id instead of integer):

      SELECT 
          a.b_id, a.value
      FROM a, b, c
      WHERE
      	a.b_id = b.id &&
          c.id = a.c_id &&
          a.b_id = "1" &&
          a.c_id = "1"
      GROUP BY a.b_id;
      

      Explain extended, version 10.1.26:
      id select_type table type possible_keys key key_len ref rows filtered Extra
      1 SIMPLE c const PRIMARY PRIMARY 27 const 1 100.00 Using index; Using temporary; Using filesort
      1 SIMPLE a ALL NULL NULL NULL NULL 1 100.00 Using where
      1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.b_id 1 100.00 Using index

      select sql_no_cache `test`.`a`.`b_id` AS `b_id`,`test`.`a`.`value` AS `value` from `test`.`a` join `test`.`b` join `test`.`c` where ((`test`.`b`.`id` = `test`.`a`.`b_id`) and (`test`.`a`.`c_id` = '1') and (`test`.`a`.`b_id` = '1')) group by `test`.`a`.`b_id`

      Explain, extended, version 10.0.32:
      id select_type table type possible_keys key key_len ref rows filtered Extra
      1 SIMPLE b const PRIMARY PRIMARY 4 const 1 100.00 Using index
      1 SIMPLE c const PRIMARY PRIMARY 27 const 1 100.00 Using index
      1 SIMPLE a ALL NULL NULL NULL NULL 1 100.00 Using where

      select sql_no_cache `test`.`a`.`b_id` AS `b_id`,`test`.`a`.`value` AS `value` from `test`.`a` join `test`.`b` join `test`.`c` where ((`test`.`a`.`b_id` = 1) and (`test`.`a`.`c_id` = '1') and (`test`.`a`.`b_id` = '1')) group by `test`.`a`.`b_id`

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            daniel.haller Daniel Häller
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: