Geek Answers Handbook

How much does it cost to untie tables in MySQL, improving performance?

I am creating a database to store some blog posts in MySQL. I recently came across this answer which reports that if you have:

  • A table to be requested regularly (e.g. a list of blog posts), but
  • One column of this table contains a large amount of data that will not be regularly available (blog content).

Then it would be better for performance if you save this content in a separate table, so when you create a listing, it will be faster.

CREATE TABLE article (
    id INT(10) UNSIGNED,
    title VARCHAR(40),
    author_id INT(10) UNIGNED,
    created DATETIME,
    modified DATETIME
);

CREATE TABLE article_text (
    id INT(10) UNSIGNED,
    body TEXT
);

This affects performance, even if the column is not part of the query:

SELECT id, title FROM article WHERE author_id=33 ORDER BY created DESC LIMIT 5

And to what extent does this become a performance issue? (A few hundred, thousands? Millions?)

+4
2

MySQL 5.5 InnoDB Barracuda. Barracuda InnoDB, ( "InnoDB" Barracuda).

Barracuda (Antelope), MySQL 768 TEXT ( ) , , TEXT, , ( ). , TEXT, ( , , ), TEXT ( 768 ), ? , , .

b-trees, , . , , node ( ).

Barracuda InnoDB TEXT ( ). ( ). , Barracuda, MySQL TEXT . , MYSQL TEXT , TEXT.

Antelope, , - ( ), TEXT. , , , .

Antelope , MySQL RAM InnoDB, TEXT. , -, , .

+7

.

" " ( " " ) , TEXT ( BLOB ..) .

TEXT, - .

TEXT, , , ( ) , JOIN .

, 200 . , . . , , , . (, "" , .)

SELECT "" INDEX (author_id, ). .

0

More articles:


All Articles