mySQL need to merge fields and get unique rows
- by jiudev
i have a database with +1 million rows and the stuktur looks like:
CREATE TABLE IF NOT EXISTS `Performance` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`CIDs` varchar(100) DEFAULT NULL,
`COLOR` varchar(100) DEFAULT NULL,
`Name` varchar(255) DEFAULT NULL,
`XT` bigint(16) DEFAULT NULL,
`MP` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `CIDs` (`CIDs`),
KEY `COLOR` (`COLOR`),
KEY `Name` (`Name`),
KEY `XT` (`XT`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;
insert into `Performance` (`id`, `CIDs`, `COLOR`, `Name`, `XT`, `MP`) VALUES
(1, '1253374160', 'test test test test test', 'Load1', '89421331221', ''),
(2, '1271672029', NULL, 'Load1', '19421331221', NULL),
(3, '1188959688', NULL, 'Load2', '39421331221', NULL),
(4, '1271672029', NULL, 'Load3', '49421341221', 'Description'),
(5, '1271888888', NULL, 'Load4', '59421331221', 'Description');
The Output should look like:
+----+------------+--------------------------+-------------+-------------+-------+-----------+---------+
| id | CIDs | COLOR | XT | MP | Name | PIDs | unqName |
+----+------------+--------------------------+-------------+-------------+-------+-----------+---------+
| 1 | 1253374160 | test test test test test | 89421331221 | | Load1 | 1,2 | Load1 |
| 3 | 1188959688 | NULL | 39421331221 | NULL | Load2 | 3 | Load2 |
| 4 | 1271672029 | NULL | 49421341221 | Description | Load3 | 4,5 | Load3 |
+----+------------+--------------------------+-------------+-------------+-------+-----------+---------+
any ideas, how i could do this as fast as possible?
I have tried with some group by, but it takes some Minutes :/
Thanks Advance
//edit:
for the solution with the group by, i needed 4 subquerys :/
//edit2:
as requested:
select id, CIDs, COLOR, XT, MP, Name, concat(PIDs,",",GROUP_CONCAT(DISTINCT id)) as PIDs, IFNULL(Name,id) as unqName from (
select id, CIDs, COLOR, XT, MP, Name, concat(PIDs,",",GROUP_CONCAT(DISTINCT id)) as PIDs, IFNULL(MP,id) as unqMP from (
select id, CIDs, COLOR, XT, MP, Name, concat(PIDs,",",GROUP_CONCAT(DISTINCT id)) as PIDs, IFNULL(XT,id) as unqXT from (
select id, CIDs, COLOR, XT, MP, Name, GROUP_CONCAT(DISTINCT id) as PIDs, IFNULL(COLOR,id) as unqCOLOR from Performance group by unqCOLOR
) m group by unqXT
) x group by unqMP
) y group by unqName
