Optimize MYSQL Select query in large table-CodePudding

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.

CodePudding user response：

TEXT columns need prefix indexes -- it's not possible to index their entire contents; they can be too large. And, if the column values aren't unique, don't use UNIQUE indexes; they won't work.

Try this:

ALTER TABLE simple ADD INDEX title_prefix (title(64));

Pro tip For columns you need to use in WHERE statements, do your best to use VARCHAR(n) where n is less than 768. Avoid TEXT and other blob types unless you absolutely need them; they can make for inefficient operation of your database server.