[#MDEV-5663] Unique indexes not used in JOINs / lacking DOCs

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.

[MDEV-5663] Unique indexes not used in JOINs / lacking DOCs Created: 2014-02-12 Updated: 2014-02-12 Resolved: 2014-02-12
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	5.5.33a
Fix Version/s:	None

[MDEV-5663] Unique indexes not used in JOINs / lacking DOCs Created: 2014-02-12 Updated: 2014-02-12 Resolved: 2014-02-12