How to create multiple entries on index based on the fields of one row? I never found a good way to index multiple fields of one row as entries of an index or simulate this feature on MySQL.

The problem arises when you have fields working as tags or similar concept. Ex.: fields names as tag1, tag2, tag3. To search lines with a specific tag fast requires that you have 3 index and do 3 separate queries on the most basic and obvious way.

Is there any way to index these 3 fields as entries of one index allowing just one search.

ID tag1 tag2 tag3
-- ---- ---- ----
01 abc  xyz  bla
02 foo  bar  ble
03 xyz  bla  bar

index
abc -&gt; 01
bar -&gt; 02 03
bla -&gt; 01 03
ble -&gt; 02
foo -&gt; 02
xyz -&gt; 01 03


Or is there any other way to do this efficiently?
If I'm understanding correctly, this query should work:

SELECT id FROM testTag WHERE LOCATE('bar', CONCAT(tag1,'.',tag2,'.',tag3)) &gt; 0;


Given this table structure:

CREATE TABLE `testTag` (
  `id` int(11) NOT NULL,
  `tag1` varchar(10) DEFAULT NULL,
  `tag2` varchar(10) DEFAULT NULL,
  `tag3` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tags` (`tag1`,`tag2`,`tag3`)
) ENGINE=InnoDB

mysql&gt; EXPLAIN SELECT id FROM testTag WHERE LOCATE('bar', CONCAT(tag1,'.',tag2,'.',tag3)) &gt; 0;
+----+-------------+---------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows |     Extra                    |    
+----+-------------+---------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | testTag | index | NULL          | tags | 39      | NULL |   15 | Using where; Using index |
+----+-------------+---------+-------+---------------+------+---------+------+------+--------------------------+