MySQL SELECT is very slow because LONGBLOB

Question

why or how solve problem on mysql.

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

when I store to this table 100 files, each 100MB, the table would have 10GB

and then try to select any row... it takes to long

SELECT name FROM xxx WHERE id = 50 LIMIT 1;

takes about 8 second

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

Answer 1

Try splitting the blobs into a separate table.

For example, you could have a table xxx containing the columns id and name, and another table xxx_data containing the columns id and data. If you only want the name, you don't need to query the xxx_data table at all; if you need both the name and the data, you can join the tables together using the shared primary key id:

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

For more information and optimization ideas, see e.g. 10 tips for optimizing MySQL queries.