Add Hint Index in queries

[fdezmc]

I need force index in my project, so I've add the method addHintIndex to set the hints indexes associated to a table. The param is an assoc array of the hintIndex(s) to be used on the query. The key is the name of the table to be hinted. The value is the hint settings or an array of hints settings to apply. Each hint setting is compose by one to three string values defining the hint and one array defining the index(s) to hint.

Here are some examples:

$query = (new \yii\db\Query())->from(['t' => 'user'])->addHintIndex(['user' => [
    ['force', 'index', ['primary']],
    ['ignore', 'index', 'order by', ['i1']],
]])->leftJoin('profile as p', 'user.id = profile.user_id')->addHintIndex(['profile' => [
    'use', 'index', ['i2']
]]);

// sql -> SELECT * FROM `user` `t` FORCE INDEX (primary) IGNORE INDEX FOR ORDER BY (i1)
 LEFT JOIN `profile` `p` ON user.id = profile.user_id USE INDEX (i2)

$users = User::find()->addHintIndex(['user' => [['use', 'index', ['primary']]]])->one();

// sql -> SELECT * FROM {{%user}} USE INDEX (primary)

I have no experience enought with other drivers than mysql, so just write functionality for mysql and sqlite drivers (the former tested the last pending for testing).

Driver overwritting can be done on:

• /yii2/framework/db/mysql/QueryBuilder.php
• /yii2/framework/db/sqlite/QueryBuilder.php (require testing)*
• /yii2/framework/db/cubrid/QueryBuilder.php (trace a message and ignore addHintIndex)*
• /yii2/framework/db/mssql/QueryBuilder.php (trace a message and ignore addHintIndex)*
• /yii2/framework/db/oci/QueryBuilder.php (trace a message and ignore addHintIndex)*
• /yii2/framework/db/pgsql/QueryBuilder.php (trace a message and ignore addHintIndex)*

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

[cebe]

As far as I see this is already possible with existing methods. no need to add anything:

// your first example:
$query = (new Query)->from([new Expression('{{%user}} USE INDEX (primary)')]);
// your second example
$query = (new Query)
       ->from([new Expression('`user` `t` FORCE INDEX (primary) IGNORE INDEX FOR ORDER BY (i1)')])
       ->leftJoin(['p' => 'profile'], 'user.id = profile.user_id USE INDEX (i2)');

[cebe]

Added a test to verify this works.

[fdezmc]

I needed hint index with activeRecord. Thought it was not possible to set an expression on from clause with ActiveRecord, now I found the way:

$user = User::find()->from([new yii\db\Expression('{{%user}} USE INDEX (primary)')]);

but the use of Expression this way, will break the abstraction layer requiring recode if you decide to change to other db driver. Adding hintIndex method could solve this issue without loosing funtionality.

[samdark]

@fdezmc different DB engines are working differently with indexes so what you're forcing in MySQL to gain extra performance may be absolute performance hog when using PostgreSQL. If you're down to this level of working with DB you're already not likely to switch easily.

[fdezmc]

Absolutely, thank you very much for your comments.

[juanmacias]

Hi @samdark , as a former DBA, query optimisation is avery important part for large application development. The Index Hinting is available in almost all database engines, is not only present in MySQL.

As a developer, you can specify the indexes you prefer in that query. The index hint can be generate to Oracle, PG, Mysql, etc... so the code will still works.

But, by the way. How many apps are developed for easy DB Engine Switch???

If you are concerned about easy switching between database, there is a lot of queries in MySQL that doesn't works in PG. Or at least, doesn't work as expected. It's imposible to make a compatible ORM without losing a lot of performance.

Adding Index Hinting, you are not breaking nothing, you are giving an option available in all databases.

[samdark]

But, by the way. How many apps are developed for easy DB Engine Switch???

Some. Installable products mainly: CMSes, CRMs etc.

[juanmacias]

I've migrated some apps from MySQL to PG in yii2 and there is a lot of work to do. For example with nulls and 0.

On Thu, Jul 7, 2016 at 11:11 AM, Alexander Makarov <[email protected]

wrote:

But, by the way. How many apps are developed for easy DB Engine Switch??? Some. Installable products mainly: CMSes, CRMs etc.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/yiisoft/yii2/issues/10869#issuecomment-231024872, or mute the thread https://github.com/notifications/unsubscribe/AEk1ZX1OIdrdggZ1PeqydqFSx8ZkkMgvks5qTMLBgaJpZM4HbM1b .

Juan Macias CEO QaShops.com 659203561

[samdark]

Absolutely.

[juanmacias]

So, you won't approve a feature to help developers optimice the SQLs on major database, just because you want Yii2 to be compatible with all databases.

A decision that helps 1% of developers and f*cks 99% developers of large applications.

[juanmacias]

Don't mind, we have managed to extend QueryBuilder to add this feature and being compatible with Yii2.

[marekpetras]

Hey @juanmacias , mind sharing the extension?

Cheers

[dynasource]

Yii2 is known for its speed. Therefore this functionality should be adopted.

[dynasource]

the examples above with Expression did not seem to work. Thats confusing. However, I managed to get it working for ->from with an example like this:

        $this->from(['USE INDEX (visitor_timestamp)' => $tableName]);

Apart from this workaround, we should have this functionality in core. I don't see why this is left out. Even if 1 or 2 DB engines don't support it, should not mean it shouldnt be implemented.

People have spent many, many hours to have their architecture ready to actually benefit from these indexes. If it then seems to be useless, this is frustrating and even more worse, the difference of a page loading in 10ms or 10 seconds.

[cebe]

the examples above with Expression did not seem to work.

@dynasource what exactly does not work for you? There is a test that checks that it works: b9880f32474bf30bd7662ee7f3465a0e58a2be2b

[dynasource]

this example did not work:

$query = (new Query)->from([new Expression('{{%user}} USE INDEX (primary)')]);

I have to recheck for the specific error

[dynasource]

I think I found the issue again: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USE INDEX (visitor_timestamp)' at line 1 The SQL being executed was: SHOW FULL COLUMNS FROM stats_normal_2016 USE INDEX (visitor_timestamp)

[cebe]

There must be something more specific you do for that error. The line you posted works fine: 6caedd1

[fdezmc]

As mentioned in #11807 this don´t work with joinWith.

// works!!!
$query = (new Query)->from([new Expression('{{%user}} USE INDEX (primary)')]);

// joinWith relation fails
$query = (new Query)->from([new Expression('{{%user}} USE INDEX (primary)')])
->joinWith('userProfile');

Note: userProfile is a relation between user and profile ON user.id = profile.user_id

ActiveQuery class method joinWithRelation takes as $parentAlias an expression resulting in:

SELECT * FROM user USE INDEX (primary) LEFT JOIN profile ON user USE INDEX (primary).id = profile.user_id

instead of

SELECT * FROM user USE INDEX (primary) LEFT JOIN profile ON user.id = profile.user_id

[juanmacias]

Well, I didn't want to share the code because is an horrible workaround (but works), I'm not proud of using it, but is the only way I've found.

How to use install it:

1. Add the code to your project (I had no time to release it as a module, any volunteer? )

2. Add in your main_local:

return [
    'components' => [
        'db' => [
            'class' => 'common\components\qamysqlconnection\Connection',

3. In the Active Models you want to enable ForceIndex, just put this:

public static function find()
   {
    return new QaActiveQuery(get_called_class());
}

or override your Query class.

How to use it With this, you will have a new fromOption() method in the ActiveQuery where you can specify whatever you want, like a "force Index" Example: Product::find()->fromOption("FORCE INDEX(supplier_reference, parent_id)")->all();

qamysqlconnection.zip

[bhagwatchouhan]

Thanks Juan for your solution.

[malsatin]

Please, resolve this problem. It hurts. And as plans for 2.1.1 are dropped, this issue became dropped too

[samdark]

Plans aren't dropped. Versioning was adjusted, that's it.

[ailiangkuai]

please resolve this problem.I found that through Active Record I have no way to use force index in mysql.

[malsatin]

I have prepared slight improvement over @juanmacias code Put this folder into ./common/extensions/qaConnection

Then you can just add use QaRecordTrait; to your ActiveRecord model and use it like that

YourModel::find()
   ->tableOption('FORCE INDEX(`' . $index . '`)')
   ->...

or

YourModel::find()
   ->from(['your_model' => 'model'])
   ->tableOption(['model' => 'FORCE INDEX(`' . $index . '`)'])
   ->...

Code: qaConnection.zip

[ailiangkuai]

Thank you for your help! @MattRh

[samdark]

https://github.com/yiisoft/yii2/pull/13607

[julianrutten]

Is there any way to do this? When I use the from option it overwrites all the joins as discussed above. Seems like this should be in to be honest. Can I help?

[samdark]

Not in Yii 2.

[julianrutten]

Shouldnt this be closed then if you have given up?