Website/database question

[arcnmx]

Originally Posted by Reincarnate

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.

Originally Posted by Reincarnate

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]

Is there a downside to just indexing everything?

[cedolad]

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]

Originally Posted by Reincarnate

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]

Originally Posted by Reincarnate

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]

Originally Posted by cedolad

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]

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

[justin_ator]

^

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]

Originally Posted by devonin

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

Originally Posted by justin_ator

^

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]

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.

[choof]

Originally Posted by Velocity

This breaks thing.

>implying

[Velocity]

Originally Posted by choof

>implying

I've deleted my account before to see what happens.

[leonid]

Some people do come back after 2+ years of coma. Nuking inactive accounts like that wouldn't turn out good anyway.

[Izzy]

8 years of inactivity then.

[Crazyjayde]

If nuking means losing all trace of forum activity from that account then I'd strongly oppose. We're still fetching some data from way back as part of the Song Corrections project and some of those have been absolutely necessary.
But then if there's a way to have access to a backup it's fine.

[choof]

Originally Posted by Velocity

I've deleted my account before to see what happens.

i meant implying that there isn't stuff on ffr that's broken regardless

[XelNya]

Originally Posted by Velocity

I've deleted my account before to see what happens.

I guess the question is, IS there a way to nuke inactive accounts that doesn't break the site? Because I do agree it COULD end badly, but tbh one or five moaners is a fair price to pay for the memory space we (from what I can tell) need.

[Untimely Friction]

I would fucking kill for the username Mikeh... I'd get this to legit vet and vetswap again probably...

On that note some accounts such as the one I just mentioned here have a last activity of 1969, as a result of the big ffr blackout, others will also have this making a purge risky business.. But I do also know this names been inactive for 10 years... AND I WANT IT

[Izzy]

I'm pretty sure accounts with 10 years of inactivity are a safe bet for users not returning. There are also plenty of accounts with virtually zero data. No posts, no games played, no logins, nothing. If you could just delete all associations to those and make their ID's reference the same blank ID to try and prevent things from breaking it would probably clean some things up.

[Untimely Friction]

Originally Posted by Izzy

I'm pretty sure accounts with 10 years of inactivity are a safe bet for users not returning. There are also plenty of accounts with virtually zero data. No posts, no games played, no logins, nothing. If you could just delete all associations to those and make their ID's reference the same blank ID to try and prevent things from breaking it would probably clean some things up.

With the downtime skewing some of those "Last visit" numbers as on that Mikeh account its kinda iffy though...