PHP's in_array vs. MySQL SELECT

Question

I need to check if some integer value is already in my database (which is growing all the time). And it should be done several thousand times in one script. I'm considering two alternatives:

1. Read all those numbers from MySQL database into PHP array and every time I need to check it, use in_array function.
2. Every time I need to check the number, just execute something like SELECT number FROM table WHERE number='#' LIMIT 1

On the one hand, searching in array which is stored in RAM should be faster than querying mysql every time (as I have mentioned, these checks are performed about a thousand times during one script execution). On the other hand, DB is growing, ant that array may become quite big and that may slow things down.

Question is - which way is faster or better by some other aspects?

Answer 1

I have to agree that #2 is your best choice. When performing a query with a LIMIT 1 MySQL stops the query when it finds the first match. Make sure the columns you intend to search by are indexed.

Answer 2

How does the number of times you need to check compare with the number of values stored in the database? If it's 1:100 then your probably better of searching in the database each time, if it's (some amount) less then preloading the list will be faster. What happened when you tested it?

However even if the ratio is low enough for it to be faster loading the full table, this will gobble up memory and could, as a result, make everything else run more slowly.

So I would recommend not loading it all into memory. But if you can, then batch the checks up to minimise the number of round trips to the database.

C.

Answer 3

It sounds like you are duplicating a Unique Constraint in code...

CREATE TABLE MyTable(
SomeUniqueValue    INT NOT NULL
CONSTRAINT MyUniqueKey UNIQUE (SomeUniqueValue));

Answer 4

querying the database is the best option, one because you said the database is growing so that means new values are being added to the table, whereis in in_array you would be reading old values. Secondly, you might exhaust the RAM alloted to PHP with very large amount of data. Thirdly, mysql has its own query optimizers and other optimizations which makes it a far better choice as compared to php