MYSQL Fast Insert dependent on flag from a seperate table
- by Stuart P
Hi all.
For work I'm dealing with a large database (160 million + rows a year, 10 years of data) and have a quandary; A large percentage of the data we upload is null data and I'd like to stop it from being uploaded.
The data in question is spatial in nature, so I have one table like so:
idLocations (Auto-increment int, PK)
X (float)
Y (foat)
Alwaysignore (Bool)
Which is used as a reference in a second table like so:
idLocations (Int, PK, "FK")
idDates (Int, PK, "FK")
DATA1 (float)
DATA2 (float)
...
DATA7 (float)
So, Ideally I'd like to find a method where I can do something like:
INSERT INTO tblData(idLocations, idDates, DATA1, ..., DATA7) VALUES (...), ..., (...)
WHERE VALUES(idLocations) NOT LIKE (SELECT FROM tblLocation WHERE alwaysignore=TRUE
ON DUPLICATE KEY UPDATE DATA1=VALUES(DATA1)
So, for my large batch of input data (250 values in a block), ignore the inserts where the idLocations matches an idLocations values flagged with alwaysignore.
Anyone have any suggestions?
Cheers.
-Stuart
Other details: Running MySQL on a semi-dedicated machine, MyISAM engine for the tables.
