# optimizer_max_sel_arg_weight

You are viewing an old version of this article. View the current version here.

## Basics

As mentioned in the Range Optimizer, ranges on multiple key parts can create a combinatorial amount of ranges.

`optimizer_max_sel_arg_weight` setting is a limit to reduce the number of ranges generated by dropping restrictions on higher key parts if the number of ranges becomes too high.

## Combinatorial number of ranges

Let's reuse the example from the Range Optimizer page.

```create table t2 (
keypart1 int,
keypart2 varchar(100),
keypart3 int,
index idx(keypart1, keypart2, keypart3)
);
```
```select * from t2
where
keypart1 in (1,2,3,4,5,6,7,8,9,10) and keypart2 in ('a','b', 'c') and keypart3 in (1,2,3,4);
```

Range optimizer will produce 10 * 3 * 4 = 120 ranges.

```select * from information_schema.optimizer_trace\G
```
```                    //...
"range_scan_alternatives": [
{
"index": "idx",
"ranges": [
"(1,a,1) <= (keypart1,keypart2,keypart3) <= (1,a,1)",
"(1,a,2) <= (keypart1,keypart2,keypart3) <= (1,a,2)",
"(1,a,3) <= (keypart1,keypart2,keypart3) <= (1,a,3)",
"(1,a,4) <= (keypart1,keypart2,keypart3) <= (1,a,4)",
"(1,b,1) <= (keypart1,keypart2,keypart3) <= (1,b,1)",
//... # 114 lines omitted ...
"(3,b,3) <= (keypart1,keypart2,keypart3) <= (3,b,3)",
"(3,b,4) <= (keypart1,keypart2,keypart3) <= (3,b,4)",
],
```

This number is fine but if your IN-list are thousands then the number of ranges can in the millions which may cause excessive CPU or memory usage.

## SEL_ARG graph

Internally, the Range Optimizer builds this kind of graph:

Vertical black lines connect adjacent "intervals" on the same key part. Red lines connect a key part to a subsequent key part.

To produce ranges, one walks this graph by starting from left most corner. Walking right "attaches" the ranges on one key part to another to form multi-part ranges. One must mind that Not all combinations produce multi-part ranges, though.