README

English | 中文

A Doctrine ORM SQL Walker for automatically adding USE INDEX or FORCE INDEX hints to MySQL queries, helping you optimize SQL performance with minimal code changes.

Features

Automatically injects USE INDEX or FORCE INDEX hints into Doctrine queries
Works only on MySQL platforms, compatible with Doctrine ORM 2.20+ and 3.0+
Simple usage via query hints
Fully compatible with native Doctrine query API

Installation

Install via Composer:

composer require tourze/doctrine-use-index-walker

Requirements:

PHP 7.4 or higher
Doctrine ORM 2.20+ or 3.0+
Doctrine DBAL 4.0+

Quick Start

<?php

use Tourze\DoctrineUseIndexWalker\UseIndexWalker;
use Doctrine\ORM\Query;

// Create a query
$query = $entityManager->createQuery('SELECT u FROM User u WHERE u.name = :name');
$query->setParameter('name', 'john');

// Add USE INDEX hint
$query->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [UseIndexWalker::class]);
$query->setHint(UseIndexWalker::HINT_USE_INDEX, 'idx_user_name');

// Or add FORCE INDEX hint
$query->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [UseIndexWalker::class]);
$query->setHint(UseIndexWalker::HINT_FORCE_INDEX, 'idx_user_name');

// Execute query
$result = $query->getResult();

Notes

Only works on MySQL database platforms
If both USE INDEX and FORCE INDEX are set, USE INDEX takes precedence
Index hint syntax depends on your MySQL version; refer to the MySQL documentation for details

How It Works

When the walker is applied to a query, it checks if the platform is MySQL and modifies the SQL accordingly:

If UseIndexWalker::HINT_USE_INDEX is set, adds USE INDEX (index_name) to the FROM clause
If UseIndexWalker::HINT_FORCE_INDEX is set, adds FORCE INDEX (index_name) to the FROM clause

Contributing

Feel free to open issues or pull requests. Please follow PSR coding standards and include tests where possible.

License

MIT License

tourze / doctrine-use-index-walker

Maintainers

Details