0

I'm using MySQL. I need to save contents of XML file to database. The size of file is usually less than 10k.

The table looks like this:

articles
-----------
id
date
writer
...
file_name
file_content (Text)
file_date

Does splitting the table improve performance when I select just date and writer? Or is there any other reason to split this table?

Sanghyun Lee
  • 21,644
  • 19
  • 100
  • 126
  • i don't think splitting any table would improve performance, you know that every extra query reduces performance. – Mihai Iorga Jul 17 '11 at 08:04
  • Whats the files contents? You could split that ab into different columns/tables if its a complicated xml structure. – Jacob Jul 17 '11 at 08:21
  • @cularis The file_content has origin data about a article in XML format. I don't need to split XML. – Sanghyun Lee Jul 17 '11 at 08:29

1 Answers1

3

This is called vertical partitioning.

Basically if your total data set is very large (say, larger than RAM), and most of your queries do not use the large file_content data, putting it in another table will make the main table much smaller, therefore much better cached in RAM, and much, much faster.

Of course retrieving the file_content will be a little slower, so it depends how often you use it.

I used this technique on a forum. I stored the posts text (bbcode parsed to HTML) in the main table, and the original posts (in bbcode) in another table. When displaying forum pages, only the main table is hit. The original post text is used only for editing posts. This divided the posts table size by 2 and avoided having to double the RAM on this server.

bobflux
  • 11,123
  • 3
  • 27
  • 27