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

Unnecessary Temporary Table Creation when using EXISTS

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.0.36, 10.1.41, 10.4.11
    • Fix Version/s: 10.1, 10.2, 10.3, 10.4
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      Linux

      Description

      I have a software product that I use across multiple different server setups that use a variety of MariaDB and MySQL installs. I've been getting a series of error reports that some of these servers have run out of space in /tmp/ due to temporary table usage.

      I went ahead and created the following case that should reproduce the scenario with a minimal number of columns and tables:

      DROP TABLE IF EXISTS `a`;
      DROP TABLE IF EXISTS `b`;
       
      CREATE TABLE `a` (
        `i` int(11) NOT NULL,
        `d` date DEFAULT NULL,
        `txt` text DEFAULT NULL,
        PRIMARY KEY (`i`),
        KEY `o` (`d`,`i`)
      ) ENGINE=InnoDB;
       
      CREATE TABLE `b` (
        `tid` int(11) NOT NULL,
        `a` int(11) NOT NULL,
        PRIMARY KEY (`tid`,`a`)
      ) ENGINE=InnoDB;
       
      INSERT INTO `a` VALUES (1,'2014-02-12', ''), (2,'2014-02-12', REPEAT('X', 10000));
      INSERT INTO `b` VALUES (1,73),(1,75);
       
      explain SELECT a.i FROM (a)
       WHERE EXISTS (select 1 from b WHERE tid='1' AND a.i = b.a) 
      ORDER BY a.d desc;
       
      DROP TABLE IF EXISTS `a`;
      DROP TABLE IF EXISTS `b`;
      

      On the majority of MariaDB installs I have tested on, I see that table `b` does the following:
      Using index; Using temporary; Using filesort

      Something of note: If I reduce the size of the dataset that goes into the second row of table `a`, a temporary table isn't used.

      e.g: REPEAT('X', 5000).

      The cutoff appears to be REPEAT('X', 8097) on several of these installs. This makes me think that there's an issue if the row length is more than 8192 bytes. This may be helpful.

      On MySQL installs (5.7.28), I get:
      Using where; Using index

      Note: There is one copy of MariaDB I have encountered running 10.1.43 that does not do this. On this build of the software, I get:
      Using index

      for table `b`.

      The differing behavior between (most) installs of MariaDB and MySQL make me think there's a bug here.

      Please let me know if I can provide any more information if needed. Thanks.

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            SeventhCycle Mark El-Wakil
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: