Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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 |
+----+-------------+---------+-------+---------------+------+---------+------+------+--------------------------+
share|improve this answer
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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