Laravel MySQL index hints scope

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

--

--

--

Software engineer

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Azure Fundamentals part 1: Describe core Azure concepts

Oracle Database — more value for money and more value for free

Espresso for UI testing

3 simple reasons why you need to use Firefox 🔥 🦊

What is the effect of Apache Nifi on data transfer and monitoring between multiple platforms?

Fuzzy logic, Explained

How to use Supervisor with Laravel on Heroku to run the queue, workers and task scheduler

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Ivan Shaburov

Ivan Shaburov

Software engineer

More from Medium

Laravel validation for multiple forms on the same page

How to make a page with pagination in Laravel

Laravel 9 One To One Relationship Example

Scaling Laravel with Serverless Redis