MySQL::Eliminating redundant elements from a table?

Posted by Legend on Stack Overflow See other posts from Stack Overflow or by Legend
Published on 2010-05-13T19:11:57Z Indexed on 2010/05/13 19:14 UTC
Read the original article Hit count: 197

Filed under:
|
|

I have a table like this:

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| v1    | int(11) | YES  | MUL | NULL    |       | 
| v2    | int(11) | YES  | MUL | NULL    |       | 
+-------+---------+------+-----+---------+-------+

There is a tremendous amount of duplication in this table. For instance, elements like the following:

+------+------+
| v1   | v2   |
+------+------+
|    1 |    2 | 
|    1 |    3 | 
|    1 |    4 | 
|    1 |    5 | 
|    1 |    6 | 
|    1 |    7 | 
|    1 |    8 | 
|    1 |    9 | 
|    2 |    1 | 
|    4 |    1 | 
|    5 |    1 | 
|    6 |    1 | 
|    7 |    1 | 
|    8 |    1 | 
|    9 |    1 | 
+------+------+

The table is large with 1540000 entries. To remove the redundant entries (i.e. to get a table having only (1,9) and no (9,1) entries), I was thinking of doing it with a subquery but is there a better way of doing this?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about beginner