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

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?


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`)

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 |

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.

  • It would still be useful to know the answer to this question assuming the schema design is valid. – Peter Eisentraut Jan 5 '11 at 11:04
  • @Peter : you can always use triggers to maintain this normalized table, so your initial table is unchanged. – Joe Jan 5 '11 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.