Partition of tables in MySQL

Question

I have read that in a case where a table has many columns, but most of the time only one of them is used (say a title column in a forum post), a way to increase performance would be a partition to two tables, where one will contain only the title and the other one will contain the other columns (such as the forum post body).

However, in case I use

select ForumTitle from Forum;

won't that be good enough to prevent the load of all columns (such as the forum post's body) to the memory, and eliminate the need of partition?

Thanks,

Joel

Tomas · Accepted Answer · 2010-12-21 10:58:48Z

2

No. The idea of partitioning the table is that the resulting table is much smaller; it fits on fewer pages. Therefore if MySQL needs to access the data freqeuently the chances that requested data containting the ForumTitles are already in memory are much bigger.

If a table contains a lot of fields, much more pages need to be cached in memory to cache the same amount of ForumTitles.

MySQL stores all columns of a row sequentially on a page and cannot load nor cache only part of a row.

answered Dec 21, 2010 at 10:58

Tomas

5,1851 gold badge38 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Partition of tables in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related