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, May 01 '22 at 12:11
I hope there must be some sort of optimization. Because in my case, its unacceptable... — Tadas V., May 01 '22 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, May 01 '22 at 12:17

O. Jones · Accepted Answer · 2022-05-01T12:23:09.600

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 01 '22 at 12:23

answered May 01 '22 at 12:16

O. Jones

103,626
17
118
172

Thank you for advice! Performance with VAR improved to 5-20s! – Tadas V. May 01 '22 at 13:33
Also the error resolved by https://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query – Tadas V. May 01 '22 at 13:33
1

Note the 768 limit on index size is different in modern versions of MySQL. The limit is now 3072 bytes (reference: https://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. – Bill Karwin May 01 '22 at 16:25
3

@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. – O. Jones May 01 '22 at 17:45

Optimize MYSQL Select query in large table

1 Answers1