Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
5.5.33a
-
None
-
None
-
None
-
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.