shipmonk / doctrine-mysql-index-hints
Custom SQL walker for Doctrine allowing usage of MySQL index hints without need of native queries
Installs: 94 334
Dependents: 0
Suggesters: 0
Security: 0
Stars: 19
Watchers: 5
Forks: 1
Open Issues: 2
Requires
- php: ^8.1
- doctrine/orm: ^3.0.0
- shipmonk/doctrine-hint-driven-sql-walker: ^2.0.0
Requires (Dev)
- doctrine/collections: ^2.1
- doctrine/dbal: ^3.7
- doctrine/event-manager: ^2.0
- editorconfig-checker/editorconfig-checker: ^10.5.0
- ergebnis/composer-normalize: ^2.42
- phpstan/phpstan: ^1.11.1
- phpstan/phpstan-phpunit: ^1.4.0
- phpstan/phpstan-strict-rules: ^1.6.0
- phpunit/phpunit: ^10.5.11
- shipmonk/composer-dependency-analyser: ^1.2.1
- shipmonk/dead-code-detector: dev-master#a718a55ae62aff39ca11caa54cb9a930118af8d7
- shipmonk/phpstan-rules: ^3.0.0
- slevomat/coding-standard: ^8.15.0
This package is auto-updated.
Last update: 2024-11-01 21:43:23 UTC
README
This library provides a simple way to incorporate MySQL's index hints into SELECT queries written in Doctrine Query Language via custom SqlWalker. No need for native queries anymore.
Installation:
composer require shipmonk/doctrine-mysql-index-hints
Simple 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(UseIndexHintHandler::class, [IndexHint::force(User::IDX_FOO, User::TABLE_NAME)]) ->getResult();
Which produces following SQL:
SELECT u0_.id AS id_0 FROM user u0_ FORCE INDEX (IDX_FOO) WHERE u0_.id = 1
See the used entity (it makes sense to put table names and index names into public constants to bind it together and reference it easily):
#[ORM\Table(name: self::TABLE_NAME)] #[ORM\Index(name: self::IDX_FOO, columns: ['id'])] #[ORM\Entity] class User { public const TABLE_NAME = 'user'; public const IDX_FOO = 'IDX_FOO'; // ... }
Combining multiple hints:
You might need to give MySQL a list of possible indexes or hint it not to use some indices. As you can see, hinting joined tables is equally simple.
->from(User::class, 'u') ->join('u.account', 'a') ->getQuery() ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, HintDrivenSqlWalker::class) ->setHint(UseIndexHintHandler::class, [ IndexHint::use(Account::IDX_1, Account::TABLE_NAME), IndexHint::use(Account::IDX_2, Account::TABLE_NAME), IndexHint::ignore(Account::IDX_3, Account::TABLE_NAME), IndexHint::ignore(Account::IDX_4, Account::TABLE_NAME), ])
Produces this SQL:
FROM user u0_ JOIN account a1_ IGNORE INDEX (IDX_3, IDX_4) USE INDEX (IDX_1, IDX_2) ON (...)
Hinting table joined multiple times:
You might need to hint only specific join of certain table. Just add which DQL alias specifies it as third argument.
->from(User::class, 'u') ->join('u.account', 'a1') ->join('u.anotherAccount', 'a2') ->getQuery() ->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, HintDrivenSqlWalker::class) ->setHint(UseIndexHintHandler::class, [ IndexHint::use(Account::IDX_1, Account::TABLE_NAME, 'a1'), // alias needed ])
Produces this SQL:
FROM user u0_ JOIN account a1_ USE INDEX (IDX_1) ON (...) JOIN account a2_ ON (...)
Advanced usage notes
- Subselects are also supported
- It works even for tables that are not present in the DQL, but are present in SQL!
- For example parent table from class table inheritance when selecting children
- Any invalid usage is checked in runtime
- Table name existence is checked, so you just cannot swap
tableName
andindexName
parameters by accident or use non-existing DQL alias - Forgotten hint or invalid arguments are also checked
- Since those checks cannot be caught by any static analysis tool, it is recommended to have a test for every query
- Table name existence is checked, so you just cannot swap
Combining with optimizer hints:
Since 3.0.0, you can combine this library with shipmonk/doctrine-mysql-optimizer-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();