Geek Answers Handbook

PHP in_array and MySQL SELECT

I need to check if some integer value was found in my database (which is constantly growing). And it needs to be done several thousand times in one script. I am considering two alternatives:

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

On the one hand, searching in an array that is stored in RAM should be faster than querying mysql every time (as I mentioned, these checks are performed a thousand times in a single script run). On the other hand, the database is growing, ant this array can become quite large, and this can slow down the work.

The question is - which way is faster or better in some other aspects?

+4
source share
4 answers

I have to agree that No. 2 is your best bet. When executing a query with LIMIT 1 MySQL stops the query when it finds the first match. Make sure that the columns you are looking for are indexed.

+1
source

It looks like you are duplicating a unique code in the code ...

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

How does the number of times you need to check compare with the number of values ​​stored in the database? If it is 1: 100, then yours probably would be better to search the database every time if it (some amount) is less than preloading the list will be faster. What happened when you tested it?

However, even if this ratio is low enough to speed up the loading of the full table, it will burn the memory and, as a result, will do the rest more slowly.

Therefore, I would recommend not loading all of this into memory. But if you can, then batch checks to minimize the number of rounds of trips to the database.

FROM.

0
source

querying the database is the best option: one, because you said the database is growing, so new values ​​are added to the table, where in in_array you read the old values. Secondly, you can run out of RAM allocated for PHP, with a very large amount of data. Thirdly, mysql has its own query optimizers and other optimizations, which makes it a much better choice compared to php

0
source

More articles:


All Articles