I'm pretty good with coding but I'm a noob at database design. This is a common problem but I didn't see an exact answer so I just want to confirm before I start coding everything.
I have a shopping site that lists a lot of products. Currently I have 6,000 products but this could grow in the future to be quite large, maybe 100,000+ some day. I list the products on the site by page and I let the user search and sort the products.
What is the most efficient way to design and query the database to search and sort products? Do I simply create one big product table and then use queries like:
SELECT *
FROM productTable
WHERE searchCriteria
ORDER BY sortColumn
LIMIT basedOnPageNumber
Will this still work quickly when I have 100,000+ items? Is there a better strategy to use?