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.