3 ms·
Ask HN: Returning lists and totals: best way?
Let's say I have an application, and each user has about 100,000-ish items stored.
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.
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?
I also need to be able to return the total amount (count) of items easily for each of these lists.
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 16y agoIn 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 16y agoUpvoted, that's likely the correct answer :)
- ScottWhigham 16y agoWhile 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.