Laravel MySQL index hints scope

2 min readMay 10, 2022

Simple library for mysql index hints and optimisations (USE INDEX, FORCE INDEX, IGNORE INDEX)

requires
* php: ^7.4|^8.0
* doctrine/dbal: ^3.0
* illuminate/database: ^8.0|^9.0
* illuminate/support: ^8.0|^9.0

Installation

composer require shaburov/laravel-mysql-index-hints-scope

How use it

Extended class Blueprint

The following methods have been added to the Blueprint class: dropIndexIfExists, hasIndex

Trait

If there is no index, then no error will occur.

Functions:

useIndex(INDEX_NAME, (JOIN|GROUP_BY|ORDER_BY), TABLE_ALIAS);forceIndex(INDEX_NAME, (JOIN|GROUP_BY|ORDER_BY), TABLE_ALIAS);ignoreIndex((INDEX_NAME|[INDEX_NAME,INDEX_NAME]),(JOIN|GROUP_BY|ORDER_BY),TABLE_ALIAS);

Constants:

IndexHintsConstants:JOIN;
IndexHintsConstants:GROUP_BY;
IndexHintsConstants:ORDER_BY;

Examples

Index hints give the optimizer information about how to choose indexes during query processing. Index hints, described here, differ from optimizer hints, described in Section 8.9.3, “Optimizer Hints”. Index and optimizer hints may be used separately or together.

Index hints apply only to SELECT and UPDATE statements.

Index hints are specified following a table name. (For the general syntax for specifying tables in a SELECT statement, see Section 13.2.9.2, “JOIN Clause”.) The syntax for referring to an individual table, including index hints, looks like this:

tbl_name [[AS] alias] [index_hint_list]
index_hint_list:
 index_hint [index_hint] ...index_hint:
 USE {INDEX|KEY}
 [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
 | {IGNORE|FORCE} {INDEX|KEY}
 [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)index_list:
 index_name [, index_name] ...

Offical MySQL documentation
Index Hints https://dev.mysql.com/doc/refman/5.7/en/index-hints.html
Github repository
https://github.com/ishaburov/laravel-mysql-index-hints-scope
Laravel documentation
https://laravel.com/docs/9.x