02-18-2014, 04:57 PM | #1 |
x'); DROP TABLE FFR;--
Join Date: Nov 2010
Posts: 6,332
|
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? |
02-18-2014, 05:20 PM | #2 |
FFR Wiki Admin
Wiki Administrator
|
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.
__________________
|
02-18-2014, 05:27 PM | #3 |
Snek
Join Date: Jan 2003
Location: Kansas
Age: 34
Posts: 9,192
|
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/ |
02-18-2014, 05:32 PM | #4 |
nanodesu~
|
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.
__________________
FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge |
02-18-2014, 05:46 PM | #5 | |
x'); DROP TABLE FFR;--
Join Date: Nov 2010
Posts: 6,332
|
Re: Website/database question
Quote:
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? |
|
02-18-2014, 05:47 PM | #6 |
nanodesu~
|
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.
__________________
FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge |
02-18-2014, 05:53 PM | #7 | |
x'); DROP TABLE FFR;--
Join Date: Nov 2010
Posts: 6,332
|
Re: Website/database question
Quote:
Or do most people just try to intelligently index their databases and just perform repeated queries from the relevant pages? |
|
02-18-2014, 05:59 PM | #8 |
nanodesu~
|
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.
__________________
FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge |
02-18-2014, 06:09 PM | #9 |
x'); DROP TABLE FFR;--
Join Date: Nov 2010
Posts: 6,332
|
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? |
02-18-2014, 06:32 PM | #10 |
Banned
Join Date: Nov 2013
Posts: 8,563
|
Re: Website/database question
i just want to say that i'm glad you guys enjoy databases because i sure as fuck don't
|
02-18-2014, 06:51 PM | #11 |
The Doctor
Join Date: Apr 2006
Age: 35
Posts: 6,145
|
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.
|
02-18-2014, 07:02 PM | #12 |
nanodesu~
|
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.
__________________
FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge |
02-18-2014, 07:10 PM | #13 |
x'); DROP TABLE FFR;--
Join Date: Nov 2010
Posts: 6,332
|
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)
|
02-18-2014, 07:15 PM | #14 |
nanodesu~
|
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 >.>
__________________
FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge |
02-18-2014, 07:52 PM | #15 | |
Doing the wrong thing the right way since 2010.
Site and Game Administrator
Join Date: Jul 2007
Posts: 1,812
|
Re: Website/database question
Quote:
It's terribly slow. |
|
02-18-2014, 08:16 PM | #16 |
Snek
Join Date: Jan 2003
Location: Kansas
Age: 34
Posts: 9,192
|
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.
|
02-18-2014, 08:29 PM | #17 | |
x'); DROP TABLE FFR;--
Join Date: Nov 2010
Posts: 6,332
|
Re: Website/database question
Quote:
I thought having lots of small queries was technically a good thing with respect to datatable normalization? |
|
02-18-2014, 08:32 PM | #18 | |
nanodesu~
|
Re: Website/database question
Quote:
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.
__________________
FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge Last edited by arcnmx; 02-18-2014 at 08:38 PM.. |
|
02-18-2014, 08:36 PM | #19 | ||
Doing the wrong thing the right way since 2010.
Site and Game Administrator
Join Date: Jul 2007
Posts: 1,812
|
Re: Website/database question
Quote:
The table layout has changed over the years so indexes on older levels don't match the new levels. Quote:
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. Last edited by Velocity; 02-18-2014 at 08:41 PM.. |
||
02-18-2014, 09:16 PM | #20 |
x'); DROP TABLE FFR;--
Join Date: Nov 2010
Posts: 6,332
|
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? |
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
|
|