Geek Answers Handbook

Duplicate Text Value Table Column

I have a database table where there is a varchar (200) column.
This is a table with historical log information, where about 99.9% of the rows in the table contain the same text message as the other row in the table.
The size of this database and the speed of the search become a problem.

So, I thought I could transfer the varchar values ​​to another table with unique text values, and then refer to this table for each row in the first table, but before I change this, I would like to know, is this an easier way to do this?
For example, setting some property in my existing table column will cause this behavior to occur automatically. So the database automatically maintains a table with unique texts. I understand that this has a big impact on deletion, but it almost never happens. I would also really like to avoid changing the program that is inserted into the log table.

I use MySQL, but if another DB can do this, it is also an opportunity to change the database to another. (MariaDB or another?)

thanks

+4
source share
2 answers

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

  • Create a table that stores varchar data, for example:

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

  • Insert varchar data

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

  • Add a new column to the source table to refer to the new log_messages table

    ALTER TABLE your_table ADD COLUMN message_id int not null;

  • Create a link

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

  • Then you can remove the varchar column from the table

    ALTER TABLE your_table DROP COLUMN your_varchar_column;

Now that you have it, I'm not sure why you are worried about the impact of performance on delete operations. Not there.

+2
source

This is the right way to do normalization, but my advice is to use a number instead of a unique text (integer) .....

+1

More articles:


All Articles