Best way to create searchable and sortable product database

database-designPHP

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?

Best Answer

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.

Related Question