Optimize MYSQL Select query in large table

Given the table:

CREATE TABLE `sample` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `vendorid` VARCHAR(45) NOT NULL,
    `year` INT(10) NOT NULL,
    `title` TEXT NOT NULL,
    `description` TEXT NOT NULL
    PRIMARY KEY (`id`) USING BTREE
)

Table size: over 7 million. All fields are not unique, except id.

Simple query:

SELECT * FROM sample WHERE title='milk'

Takes over 45s-60s to complete.

Tried to put unique index on title and description but got 1170 error.

How could I optimize it? Would be very grateful for suggestions.

If you expect there to not be a lot of rows, or you only want to return a handful anyway, then a non-unique index on title is the way to go. As this column is a TEXT datatype, you will need to constrain the length in some way, I've chosen 100.

create index sample_idx01 on sample (title (100))

An index doesn't require uniqueness by default.

StackExchange Database Administrators Q#311533, answer score: 12