Optimizing (a=b and c=d) OR (e=f and g=h)
I have a table that holds information about bi-directional references between ID numbers for information I'm displaying on a web site. The relevant fields:
`class1` varchar(32) NOT NULL default '',
`id1` mediumint(9) NOT NULL default '0',
`class2` varchar(32) NOT NULL default '',
`id2` mediumint(9) NOT NULL default '0',
`text2` varchar(255) NOT NULL default '',
KEY `class1` (`class1`,`id1`),
KEY `class2` (`class2`,`id2`)
So when someone creates a "relationship" between Article ID 13 and News ID 52, you have
class1='Article', id1=13, class2='News', id2=52
And I want to have a query so that this relationship can be found starting from either class/ID pair. The obvious query (from News ID 52, for example) is
SELECT * FROM relationships WHERE (class1='News' AND id1=52) OR (class2='News' AND id2=52);
but this isn't optimized at all (it would have to use both indexes, one for each bracketed query). So I thought I could be clever and use a self-join of some sort, like
SELECT r1.* FROM relationships as r1,relationships as r2 WHERE (r1.class1='News' AND r1.id1=52) OR (r2.class2='News' AND r2.id2=52);
thinking that each part of the join would use an optimized index on its respective query. But it still ends up scanning the whole table.
Have I not been clever enough? Is there a way to optimize such a query, or does it actually make more sense to add 2 rows of data for every relationship (one with each class/id pair in class1/id1 position?)
Or is there some more obscure way of storing/querying/indexing the information to provide a more efficient process?