Let's say I've got a primary table called products containing 1,000 records, and a lookup table called categories contain 10 records. I want to display a complete list of all 1,000 products and also the category name for each of those products.
If I build my query like so:
$sql = "SELECT products.*, CategoryName FROM products, categories WHERE ProductCategoryID=CategoryID";
... does PHP/MySQL:
A. access both the product and the categories tables 1000 times in order to display all products with category names, or
B. does it 'intelligently' build a list of categories in memory so that it doesn't have to hit the categories table again and again for each record on the products table?
If the answer is A, then I guess it would be more efficient/faster (especially with large primary tables), to read the small categories table once into an array, and then just reference the array using the ProductCategoryID I get from the products table.