2

Which is faster?

  1. I store the SQL results in an array - which means 1000 or more IDs. And then I use in_array() function 30 times to find out MyID is in the database.

  2. I check MyID is in the database and repeat it 30 times.

Test results:

48 records (MyID)

Array count: 1704 (ID) (Table size: 100 000)

Delta time:

Method 1: 0.0075759887695312

Method 2: 0.0038831233978271
Community
  • 1
  • 1
resist
  • 21
  • 1
  • 4
  • 4
    Have you tried timing it on your own? – FThompson Mar 23 '13 at 16:53
  • It will be easy enough to detect the time needed for any approach, rather than asking – Ivaylo Slavov Mar 23 '13 at 16:53
  • 2
    Why not just search the database for all your ids, and then make an in_array in that? like `'SELECT * FROM myTable WHERE MyID IN ('.implode(',', $ids).')'` – Esben Tind Mar 23 '13 at 16:55
  • 1
    Have you tried using a **single** query? E.g. `SELECT * FROM mytable WHERE id IN (1,5,2,7);`. This will return all rows from the database for users with id 1, 5, 2 and 7 **if they exist** – thaJeztah Mar 23 '13 at 16:55
  • 1
    Not yet. And I found this: http://stackoverflow.com/questions/3493105/phps-in-array-vs-mysql-select – resist Mar 23 '13 at 16:56
  • @EsbenTind LOL, you beat me to it :D – thaJeztah Mar 23 '13 at 16:56
  • <> is the slowest.Try to avoid if possible. – Arvind Mar 23 '13 at 16:57
  • @ Esben, thaJeztah: It's a bit complicated. There are several related tables, so that is not a problem MyID isn't in a table. But (sometimes) I should know about it. But thank you anyway – resist Mar 23 '13 at 17:06
  • Please try both approaches, and then answer your own question for the illumination of others. – Andy Lester Mar 23 '13 at 17:15
  • @Andy Lester: I will try – resist Mar 23 '13 at 17:22
  • Just to expand on the answer I gave to http://stackoverflow.com/questions/3493105/phps-in-array-vs-mysql-select the problem is that php arrays are implemented as hashmaps - hence for arrays with few values the cost is O(1) but this tends to O(N) as the number of collisions increases - while an indexed table in a database should give O(log(N)) consistently – symcbean Mar 23 '13 at 19:42

3 Answers3

2

I would definitely say that getting all ID's in array at one go will be much faster that querying MySQL server 30 times.

galdikas
  • 1,609
  • 5
  • 19
  • 43
  • ...but there are alternatives.. See the comments – thaJeztah Mar 23 '13 at 16:57
  • Thank you! What about the memory? How many integers may I have in that array for an acceptable performace? (I have limited resources..) – resist Mar 23 '13 at 17:07
  • well that depends on many things.... Integers 32 or 64 bit? If 32 bit you can automatically have twice as much than if it was 64 bit. Depends on how much and what type of memory available. No black or white answer really :) – galdikas Mar 28 '13 at 00:15
1

Use a (key,value) associative array instead (actually all arrays are associative in PHP but this is not the point). Set the keys of the array as ID, and each value is (for instance) 1.

$ids = array();
For all ID from database {
   $ids[ ID ] = 1;
}

To test if ID is in the array

if (isset($ids[ ID ])) {
   // yes it is 
}

using an array this way is very fast as internally the keys are stored as a tree - the search complexity is ~ O(1)

Community
  • 1
  • 1
Déjà vu
  • 28,223
  • 6
  • 72
  • 100
  • Yes, it is faster :) 1.: 0.0079531669616699; 1.1 this: 0.0025970935821533; 2: 0.0030391216278076; Thank you – resist Mar 23 '13 at 18:50
0

Neither of your proposed solutions seem right. You should look in to MySQL's IN clause. With this, you can WHERE for multiple values in a single query, like so:

SELECT something FROM table WHERE id IN(1, 4, 5, ...)
juco
  • 6,331
  • 3
  • 25
  • 42
  • But I need all 'something'. I show the datas of Table1, by ID. And if I have some more data in Table2 with the very same ID, it modifies things... I think it would be better to use JOINTs in query, but the datas of Table1 are in the memory already. – resist Mar 23 '13 at 17:21
  • Yes, sounds like you should be using a JOIN then, perhaps something like `SELECT something FROM table2 LEFT JOIN table1 ON table1.foreign = table2.id WHERE table1.id IN(1, 2, 3, ..)` – juco Mar 23 '13 at 17:28