CODEWITHSUNDEEP
mysqlindexingdatabase-designquery-optimization
Ali Faris
Ali Faris

Reputation: 18630

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

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

Upvotes: 3

Views: 2278

Answers (2)

Rick James
Rick James

Reputation: 142540

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".

Upvotes: 6

changepicture
changepicture

Reputation: 466

In my opinion, the short answer is yes. But there's more to it of course.

If you have good indexes then mysql will locate the data very fast but because the data is big then it will take a longer time to send the data.

In general smaller tables and use of numeric column types provides better performance.

And never do "SELECT *", it's just bad practice and in your case it's worst. What if you only need the title and date? Instead of transferring few data you transfer it all.

Consider splitting the table, meta data in one table and content and image in another table. This way going through the first table is very fast and only when you need the data from the second table will you access it. You will have a one-to-one relationship using this table structure.

Upvotes: 1

Related Questions