I have 2 tables: users and items. Each item has a user#. So, if I want to find only items associated with a particular user I can:
SELECT * FROM items WHERE user_id = "$user_id";
But if there are thousands of items, searching through the table will take forever, right?
Is there a better way to go about doing this? Maybe a way to store all item numbers associated with a user into a field in the users table?
Thanks, Jason
Keep your current structure but add an index on user_id. This will mean that there is no need to search through "thousands of items" when doing lookups on this column.
Storing multiple values in a single column (a violation of First Normal Form) would be a step backwards as it would drastically reduce the efficiency of the opposite query to do lookups by item_id.
No, it won't take forever. This is how it should be done, even if there were millions of records.
I noticed you quoted the user_id value... Isn't it a number field? The quotes are not needed.
Obviously the user_id should be an indexed field.
Databases (not just MySQL) are pretty fast when searching. So it will take only few milliseconds to find such a row even in millions records. Databases use "indexes" for this - so if the column "user_id" is not primary key, then create index on user_id. Otherwise it could be slow.
