How to declare a prefix index in SQLAlchemy?

I have a VARCHAR(255) column that I want to index, but this exceeds the 767 byte maximum index size in MySQL. The fix seems to be to declare an index prefix, but I can’t figure out the SQLAlchemy syntax for this.

I’m using SQLAlchemy 2.0.0 and Python 3.9. For the moment, I’m working around the problem by reducing the width of the field, but I really don’t want to resort to that in production.

class BotLog(BaseModel):
    __tablename__ = "bot_log"

    id: Mapped[int] = mapped_column(primary_key=True, autoincrement=True)
    title: Mapped[str] = mapped_column(String(190), index=True)
    timestamp_utc: Mapped[datetime]

>Solution :

not sure if i understood your issue corrctly.
In SQLAlchemy, you can use the Index class to define an index with a prefix on a specific column. Here is an example of how to create an index with a prefix of 30 characters on the "title" column in the "bot_log" table using SQLAlchemy 2.0.0:

from sqlalchemy import Index

class BotLog(BaseModel):
    __tablename__ = "bot_log"

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(190), index=True)
    timestamp_utc = Column(DateTime)
    __table_args__ = (Index("ix_title", "title", mysql_length=30),)

You can adjust the prefix size, in this case 30, to a value that works for your specific use case.
Note that you have to use mysql_length instead of postgresql_prefix

This creates an index on the first 30 characters of the "title" column, which should allow you to index the column while staying within the maximum index size limit of MySQL.