Website/database question

[Izzy]

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.

[Reincarnate]

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.

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?

[arcnmx]

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?

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.

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

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.

[Velocity]

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.

Level1, Level2, Level3, .... Level2145

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

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

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.

[Reincarnate]

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?

[arcnmx]

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?

Right. You could kinda say that indexes split up tables into many small minitables that are easy and quick to query.

Basically, if you're making multiple tables with the exact same column structure / holding the same data, you're likely doing something very wrong.

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?

Mmhm. The alternative, level_score_data9999 without a songid field results in mayhem and slowness.

[Reincarnate]

Re: Website/database question

Is there a downside to just indexing everything?

[cedolad]

Re: Website/database question

I feel like there's a main point to all of this...

But anyway, a few downsides (that I know of) are that each of the indexes requires space equal to the size and numbers of columns, as well as the size of the table. So I'd imagine with 1,500+ songs currently in-game that's a pretty big sized number.

INSERT or DELETE operations for the table need to be updated for each index in the table. Same with UPDATE operations that changes values of the index.

LOAD practically rebuilds the entire index, but using a MODIFIED BY parameter on the LOAD command can be used to override the index PCTFREE when the index was initially created.

Also each index can add alternative access paths that the optimizer may consider for a query, so that could kill compilation time.


There could be more to it though ._.

[emerald000]

Re: Website/database question

Is there a downside to just indexing everything?

It takes space. A lot of it for big tables. It also takes some time with each insert to update all the indexes.

The easiest way to build indexes is on a need basis. For every query you make, check if it uses an index and if not, create one. EXPLAIN can be a good tool to see if your indexes work as expected. PS: They often don't.

[Izzy]

Re: Website/database question

Is there a downside to just indexing everything?

If you were only ever reading and never writing to the tables then probably not. You have to do some analysis on which tables and columns are constantly getting written to the most and which are getting read the most and choose indexes based on that.

[Velocity]

Re: Website/database question

But anyway, a few downsides (that I know of) are that each of the indexes requires space equal to the size and numbers of columns, as well as the size of the table. So I'd imagine with 1,500+ songs currently in-game that's a pretty big sized number.

1,500 is nothing compared to the 1.877 million users. ;D
And 32 million score entries across every level table.

[devonin]

Re: Website/database question

Just permanently nuke every account that hasn't logged in for the preceding two years. Should cut that number down subsantially.

[justin_ator]

Re: Website/database question

^

You could even do the last four years and probably still clear out a good chunk of the db

What's the possibility of sending out account termination emails to anyone that hasn't logged in for two years?

[Velocity]

Re: Website/database question

Just permanently nuke every account that hasn't logged in for the preceding two years. Should cut that number down subsantially.

^

You could even do the last four years and probably still clear out a good chunk of the db

What's the possibility of sending out account termination emails to anyone that hasn't logged in for two years?

This breaks thing. Also Impossible, emails don't work 99% of the time.

[Untimely Friction]

Re: Website/database question

This reminds me ofwhen tass put up the last offline capable engine and it could just be pulled from cache alongside a few other files, good read here.