CODEWITHSUNDEEP
mysql
Kien.N
Kien.N

Reputation: 1

Replace a column in MySQL

I'm working with a big database (20 Gb). The structure is like that:

  `Count` int(11) NOT NULL AUTO_INCREMENT,
  `Sensor Name` varchar(100) NOT NULL,
  `Date` datetime NOT NULL,
  `Value` decimal(18,4) DEFAULT NULL

Now, I'm testing to reduce the size of database by using the 'sensorID' (int) instead of 'Sensor Name'.

 `Count` int(11) NOT NULL AUTO_INCREMENT,
  `SensorID` smallint(5) unsigned NOT NULL,
  `Date` datetime NOT NULL,
  `Value` float DEFAULT NULL,

I have an other table ('definition') to map between 'sensorID' and 'Sensor Name'. 

CREATE TABLE  `definition` (
  `SensorID` smallint(5) NOT NULL AUTO_INCREMENT,
  `Sensor Name` varchar(100))

So,how can I can replace the Sensor Name by 'sensorID' with the most efficient? Now, I'm using "SELECT" the 'Date' and 'Value' of each from old table and insert it to new table with the sensorID

INSERT INTO newtable (SensorID, `Date`, `Value`)
    SELECT 3681, `Date`, `Value` from oldtable where Sensor Name = 'abc';

with '3681' is the ID I got from 'definition' table , but it takes me a week for 50% data. JOIN is not good ideal because with 20 Gb, it needs huge resources to do.

Upvotes: 0

Views: 71

Answers (2)

Alpesh Jikadra
Alpesh Jikadra

Reputation: 1722

First create new table Sensor and populate all data in it.

update you old table's column data Sensor Name with new tables is like as follow.

UPDATE oldtable o INNER JOIN sensor s ON o.SensorName = s.SensorName
SET o.Sensorname = s.SensorId;

Then change the column name and type of oldtable's Senson Name to SensorID as follow

ALTER TABLE oldtable CHANGE Sensorname SensorId INT(11);

I hope this will solve your problem

Upvotes: 1

Jørgen
Jørgen

Reputation: 2367

You can add the SensorID column to the existing table. Then run an update on that table. Something similar to this:

update SensorData s1, SensorData s2 SET s2.SensorId = s1.Id where s1.Id = s2.Id;

But please join another table to get the real SensorID (this is just the concept). If the update on the whole table takes too much performance, batch this query with limiting the where and run it a couple of times.

Upvotes: 1

Related Questions