I have a table of archived values for each channel_id, but I also want to keep the current value for each available channel. At first I tried to put it in a separate table, but using the join in the tables for each query was incredibly slow (I made it look like joining two tables).
So, I think the best alternative is to save it in the archive data table with an extra column to indicate if this is the current value?
My question is how to add the current value to this table if the row does not exist with the same channel_id and the current column is 1, and update it if it exists?
I do not see a way to create a unique index that refers to both channel_id and current, since many channel_ids will have current = 0. Therefore, the replacement will not work. And the update will not always work, because the row may not yet exist.
Is there a better solution? A table will have millions of records, and there will be hundreds of thousands of channel_ids.
EDIT
CREATE TABLE `data` (
`date_time` decimal(26,6) NOT NULL,
`channel_id` mediumint(8) unsigned NOT NULL,
`value` varchar(40) DEFAULT NULL,
`status` tinyint(3) unsigned DEFAULT NULL,
`connected` tinyint(1) unsigned NOT NULL,
`current` tinyint(1) unsigned DEFAULT NULL,
PRIMARY KEY (`channel_id`,`date_time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `current` (
`date_time` decimal(26,6) NOT NULL,
`channel_id` mediumint(8) unsigned NOT NULL,
`value` varchar(40) DEFAULT NULL,
`status` tinyint(3) unsigned DEFAULT NULL,
`connected` tinyint(1) unsigned NOT NULL,
PRIMARY KEY (`channel_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
These are the tables that I am currently facing. I would like to either find a good way to combine them for queries (in this case, the current column in the data table is not needed) or just use the data table, and for a given channel_id find one row for this channel_id that has current = 1, and if it exists , update it with the new date value, value, status and connection. If this does not exist, add it with the same values.