

Ask HN: Returning lists and totals: best way? - petervandijck

Let's say I have an application, and each user has about 100,000-ish items stored.<p>They can add tags and properties to those, and I need to be able to return a list of items for any combination of tags/properties.<p>In sql, that would be select * from tablex where property1=x and property2=y and so on. Or a join. But that tends to be slow-ish?<p>I also need to be able to return the total amount (count) of items easily for each of these lists.<p>Using, say, mysql doesn't seem superappropriate/easy/fast/scalable for this scenario, is there a good/easy way (one of the new nosql things perhaps) to do something like this?
======
michael_dorfman
_In sql, that would be select from tablex where property1=x and property2=y
and so on. Or a join. But that tends to be slow-ish?_

Huh? It most definitely would not be the query you indicate-- that's a
terrible, terrible way to store your data in this case.

What you want is a join between the Item table and the Property table, and
that should be lightning fast, if you have your indexes built properly.

You neglect to say how many users you have, or how many concurrent queries
you're expecting, or how quickly changing the data is, or what kind of
hardware platform you're running on, so it is hard to talk about how
easy/fast/scalable mysql would be.

I'd suggest you just code a prototype using whatever database server you are
most familiar with, and run some benchmarks to test the scaling.

~~~
petervandijck
Upvoted, that's likely the correct answer :)

------
ScottWhigham
While splitting it up into multiple tables is probably a better design, that
still doesn't solve the problem of needing "to return a list of items for any
combination of tags/properties".

What I would suggest in this case is some fairly heavy-handed front-end
programming in which you determine the SQL code to execute based on the
props/tags your user has queried upon. One thing you do not want to do it to
use dynamic SQL for fear of both SQL injection and the performance. This is
done in almost every app w/ search or dropdown functionality - I'd suggest you
find some open source projects in your languages that do it and copy/learn.

