MySQL queries performance

Question

I have database catalogs with 14000 records, 100 columns and just 2 columns with type longtext. This query was really slow - more than 40 seconds

SELECT
    id,
    title,
    pdf
FROM
    catalogs
WHERE
    (shop_id = 2597)

for experiement I create new database called new_catalogs with the same structure and data but I remove 2 columns with longtext type

Running the same query was double faster - 20 seconds.

Why longtext field slow up query? How to speed up my current database which must contain these 2 columns with longtext ? I didnt select these 2 columns to get.

Using laravel queries I got the same results.

Answer 1

LONGTEXT columns are stored separately from the rest of the columns. Extra disk fetches are used to load the value.

When you separated the LONGTEXT columns out, did you then fetch the value? And that was slow, anyway?

Do you have INDEX(shop_id)?

Did Laravel do something dumb like preload the entire table?

What will you do with the PDF? If you will only be writing them to a web page, it would be more efficient in multiple ways to store it as a file, then have HTML reference it. This would probably be done via <img src=...pdf>.