How to create multiple entries on index based on the fields of one row?

Question

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 -> 01
bar -> 02 03
bla -> 01 03
ble -> 02
foo -> 02
xyz -> 01 03

Or is there any other way to do this efficiently?

Answer 1

If I'm understanding correctly, this query should work:

SELECT id FROM testTag WHERE LOCATE('bar', CONCAT(tag1,'.',tag2,'.',tag3)) > 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> EXPLAIN SELECT id FROM testTag WHERE LOCATE('bar', CONCAT(tag1,'.',tag2,'.',tag3)) > 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 |
+----+-------------+---------+-------+---------------+------+---------+------+------+--------------------------+

Answer 2

Any time you've got a numbered field name, you've not normalized your tables. I'd likely instead have a 'tag' table, that I could then join to whatever I'm tagging:

ID tag
-- ---
01 abc
01 xyz
01 bla
02 foo
02 bar
02 ble
03 xyz
03 bla
03 bar

(with the primary key being multi-column, and there being an additional index on whichever is the second field in the multi-column index)

You could always add an 'order' column if it's necessary that the tags be in a specific order.