PHPackages                             shipmonk/doctrine-mysql-optimizer-hints - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. shipmonk/doctrine-mysql-optimizer-hints

ActiveLibrary[Database & ORM](/categories/database)

shipmonk/doctrine-mysql-optimizer-hints
=======================================

Custom SQL walker for Doctrine allowing usage of MySQL optimizer hints without need of native queries

2.1.0(1y ago)923.2k↓20.9%MITPHPPHP ^8.1CI passing

Since Nov 1Pushed 1mo ago1 watchersCompare

[ Source](https://github.com/shipmonk-rnd/doctrine-mysql-optimizer-hints)[ Packagist](https://packagist.org/packages/shipmonk/doctrine-mysql-optimizer-hints)[ RSS](/packages/shipmonk-doctrine-mysql-optimizer-hints/feed)WikiDiscussions master Synced 1mo ago

READMEChangelog (4)Dependencies (14)Versions (6)Used By (0)

MySQL optimizer hints for Doctrine
----------------------------------

[](#mysql-optimizer-hints-for-doctrine)

This library provides a simple way to incorporate [MySQL's optimizer hints](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html)into SELECT queries written in [Doctrine Query Language](https://www.doctrine-project.org/projects/doctrine-orm/en/2.9/reference/dql-doctrine-query-language.html)via [custom SqlWalker](https://www.doctrine-project.org/projects/doctrine-orm/en/2.9/cookbook/dql-custom-walkers.html#modify-the-output-walker-to-generate-vendor-specific-sql). No need for native queries anymore.

### Installation:

[](#installation)

```
composer require shipmonk/doctrine-mysql-optimizer-hints
```

### Example usage:

[](#example-usage)

```
$result = $em->createQueryBuilder()
    ->select('u.id')
    ->from(User::class, 'u')
    ->andWhere('u.id = 1')
    ->getQuery()
    ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, HintDrivenSqlWalker::class)
    ->setHint(OptimizerHintsHintHandler::class, ['SET_VAR(sql_mode=ONLY_FULL_GROUP_BY)'])
    ->getResult();
```

Which produces following SQL:

```
SELECT /*+ SET_VAR(sql_mode=ONLY_FULL_GROUP_BY) */ u0_.id AS id_0
FROM user u0_
WHERE u0_.id = 1
```

Be careful what you place as optimizer hint, you are basically writing SQL there, but MySQL produces only warnings when a typo is made there.

### Use cases:

[](#use-cases)

#### Limiting / extending max execution time for a single query:

[](#limiting--extending-max-execution-time-for-a-single-query)

Any reasonable application uses some global [max\_execution\_time](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_execution_time) to avoid queries running for hours. But you may want to break this limitation for a single long-running query. Doing so by `SET max_execution_time = 10000;` is tricky as you should revert that to previous value just after the query ends. This results in complex code around it, optimizer hint does that for you for free:

```
->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, HintDrivenSqlWalker::class)
->setHint(OptimizerHintsHintHandler::class, ['MAX_EXECUTION_TIME(1000)'])
```

#### Query optimization:

[](#query-optimization)

Sometimes, [forcing some index usage](https://github.com/shipmonk-rnd/doctrine-mysql-index-hints) is not enough and you need to help MySQL optimizer to adjust the order of tables in execution plan. [Join-order optimizer hints](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-join-order) are the way to go. Simpliest usage is to force the table order to be exactly as you wrote it is using `JOIN_FIXED_ORDER()`:

```
->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, HintDrivenSqlWalker::class)
->setHint(OptimizerHintsHintHandler::class, ['JOIN_FIXED_ORDER()'])
```

#### Testing invisible index:

[](#testing-invisible-index)

When dealing with complex query optimization on production, you can only guess if the new index you thought up will help or not. Since MySQL 8.0, you can create [invisible index](https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html) (those are maintained by the engine, but not used). But you can enable invisible indexes for the query you want to test:

```
->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, HintDrivenSqlWalker::class)
->setHint(OptimizerHintsHintHandler::class, ["SET_VAR(optimizer_switch = 'use_invisible_indexes=on')"])
```

#### Enlarging group\_concat limit for a single query

[](#enlarging-group_concat-limit-for-a-single-query)

Default limit of [group\_concat\_max\_len](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_group_concat_max_len) is 1024, but you can make it bigger:

```
->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, HintDrivenSqlWalker::class)
->setHint(OptimizerHintsHintHandler::class, ["SET_VAR(group_concat_max_len = 4294967295)"])
```

### Combining with index hints:

[](#combining-with-index-hints)

Since 2.0.0, you can combine this library with [shipmonk/doctrine-mysql-index-hint](https://github.com/shipmonk-rnd/doctrine-mysql-index-hints):

```
$result = $em->createQueryBuilder()
    ->select('u.id')
    ->from(User::class, 'u')
    ->andWhere('u.id = 1')
    ->getQuery()
    ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, HintDrivenSqlWalker::class)
    ->setHint(OptimizerHintsHintHandler::class, ['MAX_EXECUTION_TIME(1000)'])
    ->setHint(UseIndexHintHandler::class, [IndexHint::force(User::IDX_FOO, User::TABLE_NAME)])
    ->getResult();
```

###  Health Score

45

—

FairBetter than 93% of packages

Maintenance66

Regular maintenance activity

Popularity33

Limited adoption so far

Community11

Small or concentrated contributor base

Maturity55

Maturing project, gaining track record

 Bus Factor2

2 contributors hold 50%+ of commits

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~140 days

Total

3

Last Release

648d ago

Major Versions

1.0.0 → 2.0.02024-01-25

PHP version history (2 changes)1.0.0PHP ^7.2 || ^8.0

2.1.0PHP ^8.1

### Community

Maintainers

![](https://www.gravatar.com/avatar/5b545e3f9d982d538f11bc42b3dc2d186f706cef92c8bc8bc8f8788b08186ea5?d=identicon)[janedbal](/maintainers/janedbal)

![](https://www.gravatar.com/avatar/7a4170ebe9281cb76be91fe00f8eee307beb3e0744dfd40ba89d9c856372c7eb?d=identicon)[shipmonk](/maintainers/shipmonk)

---

Top Contributors

[![dependabot[bot]](https://avatars.githubusercontent.com/in/29110?v=4)](https://github.com/dependabot[bot] "dependabot[bot] (21 commits)")[![janedbal](https://avatars.githubusercontent.com/u/1993453?v=4)](https://github.com/janedbal "janedbal (21 commits)")[![JanTvrdik](https://avatars.githubusercontent.com/u/175109?v=4)](https://github.com/JanTvrdik "JanTvrdik (1 commits)")

---

Tags

doctrinemysqloptimizerorm

###  Code Quality

TestsPHPUnit

Static AnalysisPHPStan

Type Coverage Yes

### Embed Badge

![Health badge](/badges/shipmonk-doctrine-mysql-optimizer-hints/health.svg)

```
[![Health](https://phpackages.com/badges/shipmonk-doctrine-mysql-optimizer-hints/health.svg)](https://phpackages.com/packages/shipmonk-doctrine-mysql-optimizer-hints)
```

###  Alternatives

[doctrine/orm

Object-Relational-Mapper for PHP

10.2k285.3M6.2k](/packages/doctrine-orm)[jdorn/sql-formatter

a PHP SQL highlighting library

3.9k115.1M102](/packages/jdorn-sql-formatter)[illuminate/database

The Illuminate Database package.

2.8k52.4M9.4k](/packages/illuminate-database)[ramsey/uuid-doctrine

Use ramsey/uuid as a Doctrine field type.

90440.3M211](/packages/ramsey-uuid-doctrine)[reliese/laravel

Reliese Components for Laravel Framework code generation.

1.7k3.4M16](/packages/reliese-laravel)[wildside/userstamps

Laravel Userstamps provides an Eloquent trait which automatically maintains `created\_by` and `updated\_by` columns on your model, populated by the currently authenticated user in your application.

7511.7M13](/packages/wildside-userstamps)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)