4

I have a database table where a have a varchar(200) column.
This is a table with historical log information where about 99.9% of the rows in the table contains the same text message as another row in the table.
The size of that database and speed when searching is becoming a problem.

So I was thinking that I could move the varchar values to another table with unique text values and then have a reference to that table for every row in the first table, but before I change this, I would like to know if there is an easier way to do this?
For instance setting some property on my existing table column that would cause this behavior automatically. So that the database automatically maintains a table with unique texts. I realize that this would of cause have a big performance impact on delete operation but that is almost never done anyway. Also I would really like to avoid changing the program that inserts into the log table.

I have been using MySQL but if another DB can do this it is also an option to change the database another one. (MariaDB or other?)

Thanks

Kennet
  • 323
  • 4
  • 12
  • What do you want to achieve here - normalising or increasing the speed? – divaka Jan 21 '14 at 09:43
  • Also if this is just a historical log, why are you logging in MySQL at first place? – divaka Jan 21 '14 at 09:44
  • My first objective is to reduce the size of the database. I need to search in the logs thats why I used a database. There is also time stamps and other colmuns in the data. – Kennet Jan 21 '14 at 09:59
  • Why don't you just make separate tables on daily, weekly or monthly basis? Because you will reduce the size now, but what about it 3 months? Or 6? – divaka Jan 21 '14 at 10:06

2 Answers2

2

There is no property or whatsoever that handles this for you, but you have a good idea there, putting the varchar data in a separate table. To do so, follow these steps.

  1. Create your table holding your varchar data like this:

    CREATE TABLE log_messages (id int auto_increment primary key, message varchar(200)) ENGINE = MyISAM ROW_FORMAT=FIXED;

  2. Insert your varchar data

    INSERT INTO log_messages (message) SELECT DISTINCT your_varchar_column FROM your_table;

  3. Add a new column in your original table to reference the new log_messages table

    ALTER TABLE your_table ADD COLUMN message_id int not null;

  4. Create the reference

    UPDATE your_table y INNER JOIN log_messages m ON y.your_varchar_column = m.message SET y.message_id = m.id;

  5. Then you can drop the varchar column from your table

    ALTER TABLE your_table DROP COLUMN your_varchar_column;

Now that you have that, I'm not sure why you're concerned about performance impact on delete operations. There's none.

fancyPants
  • 50,732
  • 33
  • 89
  • 96
1

This is the right way to do normalisation, but my advice is instead of unique text go for number(integer).....

Anto Raja Prakash
  • 1,328
  • 8
  • 12
  • Thanks for the answer. If I had to do it over, I would also do it that way. I just wanted to make sure I was not wasting time on something that the database could handle for me. – Kennet Jan 21 '14 at 10:05