Can't use force_index to exec update SQL

[0RAJA]

Python Version

3.10.11

Django Version

3.2.19

MariaDB/MySQL Version

Mysql 8.0.32

Package Version

4.11.0

Description

I hope to execute the "update" statement through "force_index", but the comment in the actual executed sql becomes "where 1", I'm not sure if this is normal behavior.
project settings

INSTALLED_APPS += (
    ...
    "django_mysql"
)
...
DJANGO_MYSQL_REWRITE_QUERIES = True
...
class OperateRecordQuerySet(QuerySetMixin):
    ...
class OperateRecordModel(models.Model):
    objects = OperateRecordModelManager().from_queryset(OperateRecordQuerySet)()

i try to use this orm code

class BusinessInfo(OperateRecordModel):
    combined_id = models.CharField(
        primary_key=True,
    )
...

delete_filter = {self.pk_field + "__in": delete_combined_ids}
BusinessInfo.model.objects.force_index("PRIMARY").filter(**delete_filter).update(is_deleted=True)

but I watch the original sql

from django.db import connection
queries = connection.queries
logger.info(queries)

this is the result

UPDATE `xxx` SET `is_deleted` = 1 WHERE ((1) AND `xxx`.`combined_id` IN ('xxx'))

for other index name,these sqls are same

I don't know if this is a normal result, please help me to confirm, thanks

[adamchainz]

The where 1 is normal behaviour, the way the SQL gets injected is a little weird.

Does UPDATE actually support query hints? If so we could add support, hopefully you'd be up for opening a PR.

[0RAJA]

Thanks for your response.

Index hints can indeed be used in update statements.
Here is the documentation..
And these are samples.

EXPLAIN UPDATE `sync_businessinfo`
SET `is_deleted` = 0,
    `updated_by` = NULL,
    `updated_at` = '2023-07-20 17:07:46.138592'
WHERE (`sync_businessinfo`.bk_biz_id IN (0));

when I use force index.

explain
UPDATE `sync_businessinfo` force index (`PRIMARY`)
SET `is_deleted` = 0,
    `updated_by` = NULL,
    `updated_at` = '2023-07-20 17:07:46.138592'
WHERE (`sync_businessinfo`.bk_biz_id IN (0));

I use force index in the update statement because the mysql optimizer will choose not to use the index when the update statement contains a large number of where statements, resulting in serious performance problems

I'm going to try to figure out how to implement force index for the update statement

[0RAJA]

I think the force index of the update statement cannot be realized through the extra provided by django orm, and it may need to be realized through raw sql

[adamchainz]

Raw SQL is not needed if we extend Django-MySQL’s feature. django_mysql.rewrite_query is where to make changes.