SQLTeam.com | Weblogs | Forums

How to avoid repeated queries?

Hello,

for a web application our users should be able to sort list entries by many different categories and combinations of catagories. Is there a way to store frequent query results (for example the most searched categories) for later refined search? How to avoid repeated queries all the time?

you might want to look at caching: client side or server side caching. which is not a SQL related issue. your web developers should know how to accomplish this.

im the developer sadly lol. thanks tho

:laughing: what web framework is it? you have tons of options.

I am using Django. I know I could use Memchache but it is not suitable because I want to query the results further. From what I've read online the best is probably to use temporary tables populated with frequent sortings.

can you provide examples and what the exact issue you are trying to solve? performance? or ...

users have tags, posts have tags (arbitrary tags).
user should find posts so the tags of the post are a subset of the users tags. Storing user/post tags in a jason datafield and doing a "select where contains/contained by" operation for every post is very expensive especially if you want to compare to 100k of posts. Thats why I need some kind of presortig altough I cannot Memchace to store for example a id list of the relevant posts because every user has a unique combination of tags. Therefore I need some sort of presorting to avoid repeated queries.

Maybe I should have columns for frequently searched tags and one column for a JSON with rare tags and indexing the frequently searched tags?

are you using Microsoft sql server database for this? if so, what data type column are you using to save the json data into?

I am using Django with PostgreSQL. In the documentation it says JSON field if you want to take a look here is the link in base64 encoding:
aHR0cHM6Ly9kb2NzLmRqYW5nb3Byb2plY3QuY29tL2VuLzMuMi90b3BpY3MvZGIvcXVlcmllcy8jcXVlcnlpbmctanNvbmZpZWxk