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

Unique indexes not used in JOINs / lacking DOCs

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Minor
        • Resolution: Not a Bug
        • Affects Version/s: 5.5.33a
        • Fix Version/s: None
        • Component/s: None
        • Labels:
          None
        • Environment:
          Debian-70-wheezy, 2xHDD in RAID1, 16GB Memory

          Description

          Considering following table that isn't changing:

          CREATE TABLE `raw_stats_lookup` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `network_id` tinyint(11) NOT NULL,
  `creative_id` int(11) NOT NULL,
  `domain` varchar(90) NOT NULL,
  `__last_used` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `_uk` (`network_id`,`domain`,`creative_id`) USING BTREE
) ENGINE=Aria AUTO_INCREMENT=688046 DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1;

          And another table we're trying to optimize:

          CREATE TABLE `raw_stats_other_copy` (
  `raw_stats_lookup_id` int(11) NOT NULL,
  `attrib_id` smallint(6) NOT NULL,
  `date` date NOT NULL,
  `raw_stats_value_id` int(11) NOT NULL,
  `impressions` int(11) NOT NULL,
  `_cf` int(11) NOT NULL,
  UNIQUE KEY `raw_stats_lookup_idxx` (`raw_stats_lookup_id`,`date`,`attrib_id`,`raw_stats_value_id`) USING BTREE,
  KEY `tag_stats_lookup_id` (`raw_stats_lookup_id`,`date`,`attrib_id`,`raw_stats_value_id`) USING BTREE
) ENGINE=Aria DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (TO_DAYS(date) DIV 10)
PARTITIONS 5 */;

          We have 2 same indexes, one is unique.

          Query 1, this one won't be using index AT ALL (look at force index)

          EXPLAIN  SELECT attrib_id, raw_stats_value_id, sum(impressions) FROM raw_stats_other_copy  FORCE INDEX(raw_stats_lookup_idxx)
INNER JOIN raw_stats_lookup
ON  raw_stats_lookup_id =  raw_stats_lookup.id
WHERE domain = 'mydomain.com'
AND date = '2014-02-10' and attrib_id = '5'
GROUP BY attrib_id, raw_stats_value_id

          Query 2:

          • We change FORCE INDEX to FORCE INDEX(raw_stats_lookup_idxx)
            Or
          • remove force index.

          => The non-unique index is used.

          Query 3:
          With FORCE INDEX removed, we drop the non-unique index from raw_stats_other_copy

          The server won't use the unique index, even if it's EXACTLY the same as non-unique index.

          Questions:

          • Why the server is treating same indexes differently depends on if they're UNIQUE or not
          • That's probably not optimizer issue, as we can't FORCE the index
          • What's internal difference between unique and non-unique index (eg. memory / file representation / data structure)
          • What each index type is suitable for, considering query optimization (as there's no data i was able to find on topic)

          Problems like that are actually hard to spot as there's no DOC in mariadb / mysql considering the topic.

          Needs some documentation if unique indexes are treated differently in JOINS, so the users will know where it'll be beneficial to add duplicated index like that.

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                serg Sergei Golubchik added a comment -

                This doesn't look like a bug. Please post this question to maria-discuss@lists.launchpad.net

                Show
                serg Sergei Golubchik added a comment - This doesn't look like a bug. Please post this question to maria-discuss@lists.launchpad.net

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    pslawek83 Slawomir Pryczek
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: