Solution for searching small/medium databases

The databases to search will range from x100 to x1000 items.

I need a search solution with PDO mysql and PHP that with best possible performance can match multiple keywords (match all) with multiple "searchable" columns in the table.

What is the performance difference of limiting queries, limiting php loops etc.?

I guess that in a smaller database such complex search would be made fast with fetching all in one query and filtering with php loops, but as soon as the db grows fetching all result开发者_开发技巧s every time must cause performance issues? This is considering that multiple queries need to be done for such search or can it be done in one query? With fulltext preferably.

Filtering with PHP loops would still be a major waste of time. Every query will fetch all 1000 rows, which have to be ripped off disk, formatted into default appearances, stuffed into the communications pipe, transmitted, received, coerced into PHP data types, and THEN filtered.

Databases are designed for filtering, whether it's on a single record or many billions of records. What you'd be using it for is just a complicated and computationally expensive storage medium. Anything you can do on the database to reduce the size of the transmitted results (even if it's over a local socket connection) is a Good Thing(tm).