1

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?

1
  • the current setup isn't too complicated.. maybe my question is an easy one. I just wanted to confirm this was the normal or optimal way to create, search, and sort a database. I was sorting through PHP after the query but it seemed to be slow. I guess when you sort and use limit through sql its a lot faster?
    – bluemoon12
    Mar 5, 2016 at 4:42

1 Answer 1

1

If an index can be defined on the search criteria then this will work OK at any scale. BTree indexes have O(log N) performance IIRC so working through 100,000 rows will be 5/3 the time needed for 1,000 rows.

However .. most systems can't fulfill that criteria. Often a user can fill as many or few search fields as she chooses. Then things get complicated. Sometimes it may be best to define separate queries, and indexes, for each combination of search values. This quickly gets large. 5! = 120; that's a lot of indexes and queries. Some DBMS have optimisations for generic searches - OPTION (RECOMPILE) in SQL Server, for example, will cause parameter embedding. Probably some combination of often-used specific queries and a generic catch-all will work.

As the list gets hugh users will expect ever fuzzier searches. At some stage a full text search on the product name and / or description may be useful. Maybe, eventually, you offload that task entirely.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.