> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/system-variables/big-query-settings.md).

# Big Query Settings

MariaDB 5.3 and beyond have a number of features that are targeted at big queries and so are disabled by default.

This page describes recommended settings for IO-bound queries that shovel through lots of records.

First, turn on Batched Key Access:

```sql
# Turn on disk-ordered reads
optimizer_switch='mrr=on'
optimizer_switch='mrr_cost_based=off'

# Turn on Batched Key Access (BKA)
join_cache_level = 6
```

Give BKA buffer space to operate on.\
Ideally, it should have enough space to fit all the data examined by the query.

```sql
# Size limit for the whole join
join_buffer_space_limit = 300M

# Limit for each individual table
join_buffer_size = 100M
```

Turn on [index\_merge/sort-intersection](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizations/index_merge-sort_intersection.md):

```sql
optimizer_switch='index_merge_sort_intersection=on'
```

If your queries examine big fraction of the tables (somewhere more than \~ 30%), turn on [hash join](/docs/server/ha-and-performance/optimization-and-tuning/query-optimizer/block-based-join-algorithms.md#block-hash-join):

```sql
# Turn on both Hash Join and Batched Key Access
join_cache_level = 8
```

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}