从DB加载完整的ID列表或一次执行一条记录？ 什么是最好的？

Question

I migrate a custom made web site to WordPress and first I have to migrate the data from the previous web site, and then, every day I have to perform some data insertion using an API.

The data I like to insert, comes with a unique ID, representing a single football game.

In order to avoid inserting the same game multiple times, I made a db table with the following structure:

CREATE TABLE `ss_highlight_ids` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `highlight_id` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
    PRIMARY KEY (`id`),
    UNIQUE KEY `highlight_id_UNIQUE` (`highlight_id`),
    KEY `highlight_id_INDEX` (`highlight_id`) COMMENT 'Contains a list with all the highlight IDs. This is used as index, and dissalow the creation of double records.'
) ENGINE=InnoDB AUTO_INCREMENT=2967 DEFAULT CHARSET=latin1

and when I try to insert a new record in my WordPress db, I first like to lookup this table, to see if the ID already exists.

The question now :)

What's preferable ? To load all the IDs using a single SQL query, and then use plain PHP to check if the current game ID exists, or is it better to query the DB for any single row I insert ?

I know that MySQL Queries are resource expensive, but from the other side, currently I have about 3k records in this table, and this will move over 30 - 40k in the next few year, so I don't know if it's a good practice to load all of those records in PHP ?

What is your opinion / suggestion ?

UPDATE #1

I just found that my table has 272KiB size with 2966 row. This means that in the near feature it seems that will have a size of about ~8000KiB+ size, and going on.

UPDATE #2

Maybe I have not make it too clear. For first insertion, I have to itterate a CSV file with about 12K records, and after the CSV insertion every day I will insert about 100 - 200 records. All of those records requiring a lookup in the table with the IDs.

So the excact question is, is it better to create a 12K queries in MySQL at CSV insertion and then about 100 - 200 MySQL Queries every day, or just load the IDs in server memory, and use PHP for the lookup ?

Answer 1

Your table has a column id which is auto_increment, what that means is there is no need to insert anything in that column. It will fill it itself.