Issue: Mysql "USE INDEX" clause?
Author: ParthGandhi
State: closed
Created: 2017-06-06 20:24:05

Is there a way to use this option? Or in general, to add arbitrary clauses to the query?

Currently i've hacked the internal `QueryCompiler` to allow adding the required clause, but thats dirty as hell cause it forces me to copy the entire method into the app code and add only 3 lines to it.


Here it is:
```python
    def generate_select(self, query, alias_map=None):
        from peewee import CompoundSelect, _StripParens, SQL, EnclosedClause, Clause, CommaClause

        model = query.model_class
        db = model._meta.database

        alias_map = self.calculate_alias_map(query, alias_map)

        if isinstance(query, CompoundSelect):
            clauses = [_StripParens(query)]
        else:
            if not query._distinct:
                clauses = [SQL('SELECT')]
            else:
                clauses = [SQL('SELECT DISTINCT')]
                if query._distinct not in (True, False):
                    clauses += [SQL('ON'), EnclosedClause(*query._distinct)]

            select_clause = Clause(*query._select)
            select_clause.glue = ', '

            clauses.extend((select_clause, SQL('FROM')))
            if query._from is None:
                clauses.append(model.as_entity().alias(alias_map[model]))
            else:
                clauses.append(CommaClause(*query._from))

        """CUSTOM CODE START"""
        if query._use_index is not None:
            clauses.append(SQL(f'USE INDEX({query._use_index})'))
        """CUSTOM CODE END"""

        if query._windows is not None:
            clauses.append(SQL('WINDOW'))
            clauses.append(CommaClause(*[
                Clause(
                    SQL(window._alias),
                    SQL('AS'),
                    window.__sql__())
                for window in query._windows]))

        join_clauses = self.generate_joins(query._joins, model, alias_map)
        if join_clauses:
            clauses.extend(join_clauses)

        if query._where is not None:
            clauses.extend([SQL('WHERE'), query._where])

        if query._group_by:
            clauses.extend([SQL('GROUP BY'), CommaClause(*query._group_by)])

        if query._having:
            clauses.extend([SQL('HAVING'), query._having])

        if query._order_by:
            clauses.extend([SQL('ORDER BY'), CommaClause(*query._order_by)])

        if query._limit is not None or (query._offset and db.limit_max):
            limit = query._limit if query._limit is not None else db.limit_max
            clauses.append(SQL('LIMIT %s' % limit))
        if query._offset is not None:
            clauses.append(SQL('OFFSET %s' % query._offset))

        for_update, no_wait = query._for_update
        if for_update:
            stmt = 'FOR UPDATE NOWAIT' if no_wait else 'FOR UPDATE'
            clauses.append(SQL(stmt))
        return self.build_query(clauses, alias_map)
```

I actually found a better way to do it:

```python
query = query.from_(SQL(f'`<table_name>` as `t1` USE INDEX(`<index_name>`)'))
```

Is this the expected way?

Unfortunately, doing something like this is not really well-supported by Peewee. Your solutions look acceptable but I agree that having to do all that stuff isn't ideal. I don't think that I'll be adding support for this any time soon, so I think you're stuck with implementing this using a less-than-ideal hack.