PHP intersect vs MySQL intersect

Question

I have a column of data in a table (1) which I have to check against the column of data in several hundred (or thousand) other tables.

Would it be faster to do this as a mysql query intersecting both tables, or to get the data from table (1) into an array, and then set the other columns of data as arrays and intersect?

It's MySQL and PHP5.2.

Answer 1

It would certainly be faster if you do it in a single query. Let your RDBMS do his job, try to use PHP as less as possible!

Answer 2

Besides the memory hit to load the data into PHP, you should consider the fact of transferring the data out of MySQL to move it into PHP before the intersection can be performed. That's a performance difference that needs to be considered if PHP and MySQL are not on the same box.

Databases are optimized for operations like this - performing the query in MySQL will also minimize the amount of data that is transferred to PHP.