DB Table TOO large!

kamy99

Hi all:

I have created a CMS website with support for 5 languages: English, French, Latin, Farsi, Kurdish.

There are quite few tables in the db, but the problematic one is the Articles table.
With only 900 entries it has already grown to 20 MEGS!!!!! 😕 😕

I only store content in it, including some HTML, but no images, files, media, etc...

The table structure is:

CREATE TABLE articles (
  article_id int(11) NOT NULL auto_increment,
  article_title_eng text NOT NULL,
  article_body_eng longtext NOT NULL,
  article_title_far text NOT NULL,
  article_body_far longtext NOT NULL,
  article_title_krd text NOT NULL,
  article_body_krd longtext NOT NULL,
  article_title_fre text NOT NULL,
  article_body_fre longtext NOT NULL,
  article_title_lat text NOT NULL,
  article_body_lat longtext NOT NULL,
  article_date date NOT NULL default '0000-00-00',
  article_author varchar(60) NOT NULL default '',
  article_publish int(11) NOT NULL default '0',
  article_cat int(11) NOT NULL default '0',
  article_comments int(11) NOT NULL default '1',
  article_feature_eng text NOT NULL,
  article_feature_far text NOT NULL,
  article_feature_krd text NOT NULL,
  PRIMARY KEY  (article_id)
) ENGINE=InnoDB AUTO_INCREMENT=905 DEFAULT CHARSET=latin1;

I do know that Farsi and Kurdish entries take more space, but didn't think this much! For example a letter in farsi traslated into code is something like:
& # 1 5 8 0;
{ I had to put space between letters so it won't get parsed}

Any ideas?

thanx
kamy

Piranha

First you should not use the datatype text for title, you are better of using varchar if at all possible. The same goes for feature. Second it seems a bit strange to have all the languages in the same table like that. There are two ways around it that I can think of:

CREATE TABLE articles (
  article_id int(11) NOT NULL auto_increment,
  article_date date NOT NULL default '0000-00-00',
  article_author varchar(60) NOT NULL default '',
  article_publish int(11) NOT NULL default '0',
  article_cat int(11) NOT NULL default '0',
  article_comments int(11) NOT NULL default '1',
  PRIMARY KEY  (article_id)
) ENGINE=InnoDB AUTO_INCREMENT=905 DEFAULT CHARSET=latin1; 

CREATE TABLE articles_language (
  article_id int(11) NOT NULL auto_increment,
  language varchar(3) NOT NULL,
  article_title varchar(200) NOT NULL,
  article_body longtext NOT NULL,
  article_feature_far varchar(50) NOT NULL,
  PRIMARY KEY  (article_id, language)
) ENGINE=InnoDB AUTO_INCREMENT=905 DEFAULT CHARSET=latin1;

The solution above will have all information that is related to all languages in one table and all information that are related to a single language in another table. The other solution is to create a new table for each language, but with the same basic idea as above. It may even be possible to use another charset for the tables that needs it if you work this way.

CREATE TABLE articles (
  article_id int(11) NOT NULL auto_increment,
  article_date date NOT NULL default '0000-00-00',
  article_author varchar(60) NOT NULL default '',
  article_publish int(11) NOT NULL default '0',
  article_cat int(11) NOT NULL default '0',
  article_comments int(11) NOT NULL default '1',
  PRIMARY KEY  (article_id)
) ENGINE=InnoDB AUTO_INCREMENT=905 DEFAULT CHARSET=latin1; 

CREATE TABLE articles_eng (
  article_id int(11) NOT NULL auto_increment,
  article_title varchar(200) NOT NULL,
  article_body longtext NOT NULL,
  article_feature_far varchar(50) NOT NULL,
  PRIMARY KEY  (article_id)
) ENGINE=InnoDB AUTO_INCREMENT=905 DEFAULT CHARSET=latin1;

Note that these suggestions is to handle the database better, it won't take less space. But let's face it, 20 MB in a database is not much.

kamy99

Hi:

thanx for your suggestions. As you said these changes would not affect the db size. That's the most important thing for me right now.
We do not have a problem with disk space, but backing up a 20 meg table is a hassle and as this site is growing quickly I'm terrified to think what happens when we have 2-3000 articles!

Piranha

kamy99 wrote:
Hi:

thanx for your suggestions. As you said these changes would not affect the db size. That's the most important thing for me right now.
We do not have a problem with disk space, but backing up a 20 meg table is a hassle and as this site is growing quickly I'm terrified to think what happens when we have 2-3000 articles!

Look at using the second example and change charset in the languages that don't use Latin1 normally. I can't say that it would shrinken the size of the database, but it might do it.

But tbh I can't see any problem with backing up 20 MB of data. A CD can store 700MB, a DVD 4,7 GB. As long as the database is not bigger than that it should be fairly easy to back up using one of those medias.

MarkR

Why don't you store everything in utf-8? Why use html entities? Surely it stops searching / indexing from working properly.

The overheads in a mysql database should be fairly small under normal circumstances- but the database will take up at least as much space as the amount of text which is in it, never any less.

Is the twenty megs the size of the backup file or the data files? If it's the data files, these include indexes.

I also encourage you to rethink your design to put each language in its own row rather than its own column; this will make it much more extensible.

Mark