Website/database question
 

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?

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.

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/2...neric-version/

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.

Re: Website/database question
 

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?

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.

Re: Website/database question
 

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?

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.

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?

Re: Website/database question
 

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

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.

Re: Website/database question
 

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.

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)

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 >.>

Re: Website/database question
 

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.

Re: Website/database question
 

Maybe you could do some kind of massive join of all the levels tables and then query it all at once.

Re: Website/database question
 

What's the smart way to do this? When you say it's a "separate level" in the database, do you mean a separate table? Like Level1 table, Level2 table, etc.

I thought having lots of small queries was technically a good thing with respect to datatable normalization?

Re: Website/database question
 

The smart way is to not do this, just have one single table, and set up an index on the levelid/whatever to allow queries to work as well as if they were in separate tables, without the retardedness that comes with actually using separate tables.

This won't improve anything due to the lack of an index on the element you're querying (in levelranks case, userid). It would probably also max out memory usage and fail anyway.

Re: Website/database question
 

Level1, Level2, Level3, .... Level2145

The table layout has changed over the years so indexes on older levels don't match the new levels.

It's generally bad.

Also since I've only learned this stuff working on FFR, I have no idea whats better. I just change the queries and time the results and pick the consistently fast one.

Re: Website/database question
 

I find that interesting -- so you're saying it's actually better to have it all in one table with well-chosen indexes?

How do you then decide what to put into another table and what to lump together?

Although I guess it makes sense: If you have a table called "level_score_data" or something, it would just contain a key identifier (song ID or something) with the userid (person who played it) and the corresponding score. Then you could just query that to calculate the levelranks, yes?