MySQL SELECT is very slow because LONGBLOB

Question

why and how to solve the problem in mysql.

table xxx
-> id primary key
-> name varchar 255
-> data longblob

when I store 100 files in this table, each 100 MB, the table will have 10 GB

and then try to select any row ... it lasts

SELECT name FROM xxx WHERE id = 50 LIMIT 1;

takes about 8 seconds

My problem is probably that mysql reads the whole line before it returns name, which is only 255 characters ... so when I want to list the names of 100 files, mysql reads 10 GB and returns about 2 KB of result.

Answer 1

Try dividing the drops into a separate table.

, xxx, id name, xxx_data, id data. , xxx_data; , , id:

SELECT id, name, data
FROM xxx JOIN xxx_data USING (id)
WHERE id = ...

., , 10 MySQL.