MySQL - optimising selection across two linked tables
- by user293594
I have two MySQL tables, states and trans:
states (200,000 entries) looks like:
id (INT) - also the primary key
energy (DOUBLE)
[other stuff]
trans (14,000,000 entries) looks like:
i (INT) - a foreign key referencing states.id
j (INT) - a foreign key referencing states.id
A (DOUBLE)
I'd like to search for all entries in trans with trans.A 30. (say), and then return the energy entries from the (unique) states referenced by each matching entry. So I do it with two intermediate tables:
CREATE TABLE ij SELECT i,j FROM trans WHERE A30.;
CREATE TABLE temp SELECT DISTINCT i FROM ij UNION SELECT DISTINCT j FROM ij;
SELECT energy from states,temp WHERE id=temp.i;
This seems to work, but is there any way to do it without the intermediate tables? When I tried to create the temp table with a single command straight from trans:
CREATE TABLE temp SELECT DISTINCT i FROM trans WHERE A30. UNION SELECT DISTINCT j FROM trans WHERE A30.;
it took a longer (presumably because it had to search the large trans table twice. I'm new to MySQL and I can't seem to find an equivalent problem and answer out there on the interwebs.
Many thanks,
Christian
