Optimize MYSQL Select query in large table

Question

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.

Just a SELECT * FROM sample will take 45s-60s, so I do not see the problem... — Luuk
– Luuk, Commented May 1, 2022 at 12:11
I hope there must be some sort of optimization. Because in my case, its unacceptable... — Tadas V.
– Tadas V., Commented May 1, 2022 at 12:12
@TadasV. can you try create index on sample(title(255)); and then run 2 versions of your query? select ... where title = 'milk' and select ... where title like 'milk%' and see if they are any faster? — zedfoxus
– zedfoxus, Commented May 1, 2022 at 12:17

O. Jones · Accepted Answer · 2022-05-01 12:23:09Z

2

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.

edited May 1, 2022 at 12:23

answered May 1, 2022 at 12:16

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tadas V. Over a year ago

Thank you for advice! Performance with VAR improved to 5-20s!

Tadas V. Over a year ago

Also the error resolved by stackoverflow.com/questions/10563619/…

Bill Karwin Over a year ago

Note the 768 limit on index size is different in modern versions of MySQL. The limit is now 3072 bytes (reference: dev.mysql.com/doc/refman/8.0/en/innodb-limits.html). Also keep in mind it's in bytes, not characters. The default character set is now utf8mb4, supporting up to 4 bytes per character.

O. Jones Over a year ago

@BillKarwin That's right. i mentioned the number 768 because it is 3072 / 4. That's the longest VARCHAR() indexable without a prefix when the charset is utf8mb4.

Collectives™ on Stack Overflow

Optimize MYSQL Select query in large table

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related