Does text or blob fields slow down the access to the table

Question

I have table contains text and blob field and some other , I'm wondering if using these kind of data types would slow down the access to table

CREATE TABLE post 
(
    id INT(11),
    person_id INT(11) ,
    title VARCHAR(120) ,
    date DATETIME , 
    content TEXT,
    image BLOB ,
);

let's say i have more than 100,000 posts and i want to do some query like

SELECT * FROM post WHERE post.date >= ? AND post.person_id = ?

would the query be faster if the table does not contains TEXT and BLOB fields

Answer 1

Yes or no.

If you don't fetch the text/blob fields, they don't slow down SELECTs. If you do, then they slow things down in either or both of these ways:

• In InnoDB, TEXT and BLOB data, if large enough, is stored in a separate area from the rest of the columns. This may necessitate an extra disk hit. (Or may not, if it is already cached.)
• In complex queries (more complex than yours), the Optimizer may need to make a temporary table. Typical situations: GROUP BY, ORDER BY and subqueries. If you are fetching a text or blob, the temp table cannot be MEMORY, but must be the slower MyISAM.
• But, the real slowdown, is that you probably do not have this composite index: INDEX(person_id, date). Without it, the query might choose to gather up the text/blob (buried in the *) and haul it around, only to later discard it.

Action items:

• Make sure you have that composite index.
• If you don't need content for this query, don't use *.

If you need a TEXT or BLOB, use it; the alternatives tend to be no better. Using "vertical partitioning" ("splitting the table", as mentioned by @changepicture) is no better in the case of InnoDB. (It was a useful trick with MyISAM.) InnoDB is effectively "doing the split for you".