3

We have a "notes" table and some of the queries are getting very slow even though we're not searching on the note contents. We're wondering if moving the note content into a separate table would speed things up?

I wouldn't think separating the content would matter -- aren't TEXT fields essentially just pointers to somewhere else on disk? Or does table size matter?

Does InnoDB handle this better?

Improve this question
3
  • Do you mean MyISAM?
    – Rick James
    Commented Jun 9, 2015 at 19:01
  • @RickJames Yes, we're using MyISAM right now but we have no qualms about switching to a different storage engine if it'll speed things up. However, merely changing the storage engine with no changes to the structure/index didn't seem to help any. I just wanted some background info here so I could better diagnose the problem, which you helpfully provided, so thank you.
    – mpen
    Commented Jun 9, 2015 at 20:18
  • InnoDB is generally better than MyISAM. Here is a discussion of the things that should be tweaked when converting.
    – Rick James
    Commented Jun 9, 2015 at 20:28

3 Answers 3

Reset to default
1

MyISAM only -- Normally a record is a continuous stream of bytes in the .MYD file. This includes TEXT and BLOB columns. An index has a byte offset (or record number) to point into the .MYD file.

After row(s) have been DELETEd, there can be holes in the .MYD. MyISAM prefers to fill in the holes before appending to the .MYD. However the hole(s) may not be big enough. In this case, part of a record is stored in the first hole and a link (byte offset) is stored to the next part of the record. This linking goes on as long as necessary.

So, if there is a lot of "churn" in the table (deletes+inserts, etc), then new INSERTs can be very inefficiently stored. That is, they may be fragmented and scattered. This is one of the few cases where OPTIMIZE TABLE is useful for a MyISAM table. The OPTIMIZE will rebuild the table by effectively reading and rewriting each row into a new (tmp) .tmd, later to be renamed to .MYD. This defragments rows and gets rid of any remaining gaps.

There is no "block" structure to the .MYD. In contrast, the .MYI, holds all indexes, including the PRIMARY KEY, in 1KB BTree blocks.

The only caching for data is what the OS provides. That tends to be in 4GB chunks that have zero correlation to record boundaries of the rows in the table.

When to have parallel table -- The question is really about this. (But I needed to say the above first.)

It is hard to give a clear indication of when it is useful to manually split the table by columns.

Case 1: You do a lot of searching through the table without touching the TEXT field. Having the big TEXT field(s) else where avoids stepping over those big cow paddies. The JOIN to get the TEXT for one row is not that expensive.

Case 2: You are always doing LIKE on the TEXT field. Then vertical partitioning slows things down.

I have done Case 1 in a few projects.

Case 1 can be further enhanced by this trick:

SELECT a.stuff, b.text
    FROM ( SELECT ... FROM main_table ... LIMIT 1 ) a
    JOIN text_table b USING (id);

Notes:

  • The subquery hits a lot of rows, but then decides on a few (via LIMIT or GROUP BY, or...);
  • Any tmp tables are not big (since the TEXT is not in it);
  • Only 1 probe into the parallel table are needed.

Another trick is to Compress the text field (and put it into a BLOB).

  • Do the compression in the client, not the server
  • 3:1 shrinkage is typical for text
  • The space savings (hence I/O savings) is likely to more than compensate for the CPU time in compression if you have a big table.

(The two "tricks" can be applied to InnoDB, to.)

Improve this answer
4
  • Thank you. This is good information. A couple questions: what do you mean by "client"? Do you mean if the browser, via JS, assuming a web app? Or do you mean on the app server (e.g. PHP), as opposed to the SQL server? Do you recommend gzip for compression, or something else? I've noticed for small bits of text gzip will actually increase the size; would you conditionally compress it or no?
    – mpen
    Commented Jun 9, 2015 at 20:15
  • 1
    If your TEXT is consistently short, don't bother to compress. I use whatever is convenient in the client (PHP: gzcompress()). "Client": A "client" sends SQL to the MySQL "server".
    – Rick James
    Commented Jun 9, 2015 at 20:26
  • One more question, regarding your SQL example. If I just did SELECT a.stuff, b.text FROM main_table a JOIN text_table b WHERE a.criteria ORDER by a.col LIMIT 1 would MySQL not be smart enough to limit the result set using only a before joining b, if neither the WHERE or ORDER clause contains any fields from b?
    – mpen
    Commented Jul 7, 2015 at 21:17
  • It can stop short only if there is an adequate INDEX(criteria, col).
    – Rick James
    Commented Jul 7, 2015 at 22:18
3

In MyISAM records are stored in blocks. There are like 20 different block types, some of can be really large. Those are used for BLOB/TEXT values. I never saw that MyISAM record stores a pointer to a BLOB (but I would not insist).

In InnoDB BLOB/TEXT values are also a part of the record and stored in-page as long as total record size not more than ~7k. Otherwise 768 first bytes are stored in-page and remaining part is in external pages. (In Barracuda format it just stores a 20 bytes pointer to the external page).

Improve this answer
4
  • So... moving the content to a separate table might help then? That means MySQL has to load less data into memory when doing searches and sorts because the record size will be smaller?
    – mpen
    Commented Jun 5, 2015 at 21:48
  • 2
    There are two many moving parts to give any recommendations here. What are your SELECTs, how large are BLOB fields, which InnoDB format are you going to use or do you prefer to stick to MyISAM.
    – akuzminsky
    Commented Jun 5, 2015 at 21:54
  • This 'answer' is also quoting InnoDB structures that do not apply to MyISAM.
    – Rick James
    Commented Jun 9, 2015 at 19:01
  • I don't think there are 20 block types anywhere. 7K should perhaps be 8K, but only for InnoDB.
    – Rick James
    Commented Jun 9, 2015 at 20:30
1

TEXT fields have what the MySQL Documentation (Compressing BLOB, VARCHAR and TEXT Columns) calls "off-column storage"

In a clustered index, BLOB, VARCHAR and TEXT columns that are not part of the primary key may be stored on separately allocated (“overflow”) pages. We call these “off-page columns” whose values are stored on singly-linked lists of overflow pages.

That link has more information on using compression. So does Percona.

I have some posts about InnoDB Storage in comparison to how PostgreSQL does it

In terms of MyISAM, you must use ROW_FORMAT=COMPRESSED to improve storage usage

Improve this answer
1
  • 1
    Caution: Most of what is said in this 'answer' applies only to InnoDB. The OP seems to be asking about MyISAM.
    – Rick James
    Commented Jun 9, 2015 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.