ttunstall ttunstall - 2 months ago 5
MySQL Question

How do I create a relation between tables using an interval comparison in MySQL?

I would like to create a relationship between two tables based on whether a value in one table falls in a an interval in the other. One table 1 is ~16000 rows:

name | start | end
-----------------------------------------
someName | startPosition | endPosition


table 2 is ~20000000 rows:

id | location
--------------------------
someID | positionInteger


Each id falls in the interval of exactly one name, but each name can have many ids associated with it.

I would like to add a new index to table 2 so that it becomes:

id | location | name
---------------------------------
someID | positionInteger | someName


I've tried doing:

ALTER TABLE table2 ADD INDEX name (name);


With a bit of python I can get all the names in the database and then for each name and interval:

SELECT someID FROM table2 WHERE location >= startPosition AND location <= endPosition


Then I can loop through the resulting IDs and:

UPDATE table2 SET name = 'someName' WHERE id = 'someID'


This works but is very slow. Is there a more efficient way to do this using MySQL and avoiding multiple loops?

Answer

Join the tables

SELECT t2.id, t2.location, t1.name
FROM Table1 AS t1
JOIN Table2 AS t2 ON t2.location BETWEEN t1.startPosition AND t1.endPosition

You shouldn't be adding an index to table 2, you should be adding a column:

ALTER TABLE Table2 ADD COLUMN name VARCHAR(32); -- Replace this with the actual size

Then you can update all the rows with a similar join:

UPDATE Table2 AS t2
JOIN Table1 AS t1 ON t2.location BETWEEN t1.startPosition AND t1.endPosition
SET t2.name = t1.name