You are here

Home

Architecture and Design decisions

When it comes to MariaDB/MySQL Database consulting engagements we often see, that our customers are very confused by many different technologies advertised to them.

Below you will find some simple concepts and ideas we use during our consulting engagements to solve some issues and answer some questions.

General problem solving approach

  • 1. Written description of the problem to solve. This helps to learn about your problem and better understand your problem.
  • 2. Searching for a suitable method, technology or architecture solving the described problem.
  • 3. Evaluating a product or solution which is specialised solving the described problem. Check if the product/solution matches your problem.
    Caution: In this topic we are strongly MariaDB/MySQL biased!
  • 4. Do a Proof of Concept (PoC) to verify the chosen solution will fix your described problem (or not).

Splitting Database Tables (BLOB/TEXT)

Sometimes it might be useful to split 1 entity (table) into 2 separate tables. This makes sense when you have either:

  • hot (frequently used) and cold (rarely used) data stored in the same table
  • or when you have huge objects (TEXT, BLOB, JSON, etc.) stored

in the table.

This results in something like this:

+-----+-----+-----+-----+------+    +-----+-----+-----+-----+   +-----+------+
| id  | c1  | c2  | c3  | text |    | id  | c1  | c2  | c3  |   | id  | text |
+-----+-----+-----+-----+------+ -> +-----+-----+-----+-----+ + +-----+------+
|   1 | ... | ... | ... | ...  |    |   1 | ... | ... | ... |   |   1 | ...  |
+-----+-----+-----+-----+------+    +-----+-----+-----+-----+   +-----+------+

Many Databases already try to solve this technically:

Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened.

and

It is worth to note I use BLOB here in a very general term. From storage prospective BLOB, TEXT as well as long VARCHAR are handled same way by Innodb. This is why Innodb manual calls it "long columns" rather than BLOBs.

See also:


MariaDB/MySQL Architectures Overview

MariaDB/MySQL Architectures Overview

tags: 
architecture
design
consulting
blob