Add support for index / query hints in query builder

[joe-meyer]

I think that we should add support for index hinting in the query builder. I've tried simply adding the hint to the FROM clause, but this tends to break things when doing joins because it tries to add the hint string to those as well (which causes parse errors on the database side.

A couple examples of index hints:
MySQL uses USE INDEX (index_name,index2_name)
MsSQL uses WITH( INDEX(index_name))

I think that this should probably be it's own function on the query builder so that you can easily change the index based on a where clause you might be building or other conditionals. Just spit balling here:

/**
 * @param string|array $indexName 
 * @param null|string $table The table name or alias to apply the index hint to, defaults to first FROM table
 * @return string
 */
function tableHint($indexName, $table = null) {
    ...
}

And could see it being used sort of like this:

public static function queryEmployees($location  = null, $name = null)
{
    $employees = Employee::find();
    if($location !== null) {
        $employees->andWhere(['location' => $location])->tableHint('location_index');
    }


    if($name) {
        $employees->andWhere(['name' => $name])->tableHint('name_index');
    }

    return $employees->all();
}

[cbhp]

"use index" is sometimes not enough. Then I would like to use "force index":
https://dev.mysql.com/doc/refman/5.1/en/index-hints.html

[Sarke]

+1.

Currently this can be accomplished using an expression, but it's kinda verbose and not database agnostic.

Something like so:

$rows = (new \yii\db\Query())
	->select('*')
	->from(new \yii\db\Expression('`tablename` USE INDEX (`my_index`)'))

[samdark]

@Sarke is there a common syntax for forcing index usage across different databases?

[uaoleg]

I've created a PR for this #19946