Website/database question

[Reincarnate]

Basic question but I am curious as to the responses:

On a website, if you have a large database in the backend, and a profile page that populates with data from the backend, is it generally the case that the profile page must requery the database every single time the page is loaded?

What if the database is huge? Won't this result in lots of overhead per page refresh?

[noname219]

Re: Website/database question

Well, there are some websites that manage to do a few things to avoid overloads.
One example is RYM and their charts. Basically, every 1-2 week, a snapshot is taken of every single album in the database, they use that data to produce a ranking with an algorhythm that weights user ratings. That's all I know.

[Izzy]

Re: Website/database question

You should ask Houkounchi how we manage smo queries because the database is getting huge at this point. Something with indexing fields that get queried a lot and caching.

Random article about database optimizing.

http://www.techfounder.net/2011/03/25/database-profiling-and-optimizing-your-database-the-generic-version/

[arcnmx]

Re: Website/database question

A database's whole job is to perform well despite the size of its data. Indexing key fields helps queries speed up without row count or data size hindering much, etc. A database can cache commonly-requested data, or a web server can cache the resulting generated page and serve it repeatedly (or parts of it) without the backend running again until the data's been changed.

[Reincarnate]

Re: Website/database question

A database's whole job is to perform well despite the size of its data. Indexing key fields helps queries speed up without row count or data size hindering much, etc. A database can cache commonly-requested data, or a web server can cache the resulting generated page and serve it repeatedly (or parts of it) without the backend running again until the data's been changed.

This is what I had in mind -- I figured that ideally, indexing should be making the queries fast anyway.

Also do you have examples or more to elaborate on by "A database can cache commonly-requested data, or a web server can cache the resulting generated page and serve it repeatedly (or parts of it) without the backend running again until the data's been changed."?

Doesn't this mean you are double-storing data in some form?

[arcnmx]

Re: Website/database question

It often means you cache the resulting html for commonly-requested pages in ram so you can just shove it right out instead of running php/queries/etc. to generate it again. Look up things like memcached.

[Reincarnate]

Re: Website/database question

It often means you cache the resulting html for commonly-requested pages in ram so you can just shove it right out instead of running php/queries/etc. to generate it again. Look up things like memcached.

Does this generally result in a lot of storage / is this sort of thing commonly used?

Or do most people just try to intelligently index their databases and just perform repeated queries from the relevant pages?

[arcnmx]

Re: Website/database question

It's very common, yes. Whether it results in a lot of memory usage is up to you, these things are often configurable to cap at a certain amount and start discarding old data. Some solutions are completely automatic and cache entire pages, some like memcached let you decide how and when you want to cache data.

In general it's a great idea for things that get requested often but don't change often. Take FFR's homepage for example, caching the news posts instead of querying each time would benefit the site, the latest posts list, the video db list, things like that.

[Reincarnate]

Re: Website/database question

So for example, how does the Levelranks page work and why does it take so long?

What are the main challenges and how could it be done better?

[choof]

Re: Website/database question

i just want to say that i'm glad you guys enjoy databases because i sure as fuck don't

[TheSaxRunner05]

Re: Website/database question

I'd love there to be a separate raw score Database so that raw scores would actually mean something. I know there is a raw score high scores page, but those aren't supplanted by raw score improvements.

[arcnmx]

Re: Website/database question

So for example, how does the Levelranks page work and why does it take so long?

What are the main challenges and how could it be done better?

Let's just say the entire thing could be done better. Due to poor design, counting scores is slow which is why the site caches your AAA/FC/etc. counts on your profile and the levelrank page updates it.

[Reincarnate]

Re: Website/database question

What makes the design poor? (I am trying to get at the difference between poor vs. good design when it comes to something involving massive amounts of data such as the levelranks)

[arcnmx]

Re: Website/database question

A lack of indexes, mainly, and a design that prevents easily switching to them. Dunno how much I'm allowed to disclose so I'm just going to leave it at that >.>

[Velocity]

Re: Website/database question

So for example, how does the Levelranks page work and why does it take so long?

What are the main challenges and how could it be done better?

Each of FFR's levels are stored as a separate level in the database, so when doing levelranks, it grabs the entire song list, then loops every level getting your score from each table. So it ends up doing a massive amount of small queries. This is also why that page is cached for 15 minutes each time it's load.

It's terribly slow.