Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL)
-
None
Description
Consider a table
create table t1234 (
|
campaignid varchar(32) not null,
|
adgroupid varchar(32) not null,
|
col1 int not null,
|
PRIMARY KEY (campaignid,adgroupid),
|
UNIQUE KEY adgroupid (adgroupid)
|
);
|
and a query in form
explain
|
select * from t1234
|
where
|
campaignid='Finland' AND adgroupid IN ('Lahti','Imatra')
|
OR
|
campaignid='Russia' AND adgroupid IN ('Petersburg', 'Moscow')
|
OR
|
campaignid='Sweden' AND adgroupid IN ('Stockholm', 'Uppsala')
|
OR
|
campaignid='Germany' AND adgroupid IN ('Berlin', 'Frankfurt')
|
OR
|
campaignid='Belgium' AND adgroupid IN ('Brussels', 'Gent')
|
OR
|
campaignid='Portugal' AND adgroupid IN ('Lisbon', 'Porto')
|
...
|
The query
- takes a lot of time to run (more details to follow about this).
- May switch to using full table scan when it is still better to use the range access.
The reason seems to be that MariaDB's optimizer is trying to construct index_merge access plans which are not particularly meaningful ( adding ignore index (adgroupid) resolves the issue).
Attachments
Issue Links
- relates to
-
MDEV-9764 MariaDB does not limit memory used for range optimization
- Closed