Old 02-18-2014, 10:42 PM   #21
arcnmx
nanodesu~
Retired StaffFFR Veteran
 
arcnmx's Avatar
 
Join Date: Jan 2013
Location: Ontario, Canada
Posts: 503
Send a message via Skype™ to arcnmx
Default Re: Website/database question

Quote:
Originally Posted by Reincarnate View Post
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.

Quote:
Originally Posted by Reincarnate View Post
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.
__________________


FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge
arcnmx is offline   Reply With Quote
Old 02-19-2014, 08:08 AM   #22
Reincarnate
x'); DROP TABLE FFR;--
Sectional ModeratorFFR Veteran
 
Reincarnate's Avatar
 
Join Date: Nov 2010
Posts: 6,343
Default Re: Website/database question

Is there a downside to just indexing everything?
Reincarnate is offline   Reply With Quote
Old 02-19-2014, 08:25 AM   #23
cedolad
moonchild~
FFR Simfile AuthorFFR Veteran
 
cedolad's Avatar
 
Join Date: Jan 2007
Location: .
Age: 26
Posts: 7,057
Send a message via Skype™ to cedolad
Default 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 ._.
__________________
cedolad is offline   Reply With Quote
Old 02-19-2014, 08:44 AM   #24
emerald000
the Mathemagician~
FFR Veteran
 
emerald000's Avatar
 
Join Date: Nov 2005
Location: Quebec City
Age: 29
Posts: 1,320
Send a message via Skype™ to emerald000
Default Re: Website/database question

Quote:
Originally Posted by Reincarnate View Post
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.
emerald000 is offline   Reply With Quote
Old 02-19-2014, 10:13 AM   #25
Izzy
Frau Bow
FFR Simfile AuthorFFR Veteran
 
Izzy's Avatar
 
Join Date: Jan 2003
Location: Kansas
Age: 29
Posts: 9,197
Send a message via AIM to Izzy Send a message via MSN to Izzy Send a message via Skype™ to Izzy
Default Re: Website/database question

Quote:
Originally Posted by Reincarnate View Post
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.
__________________
Join SMO IRC. irc.rizon.net#smo
Izzy is offline   Reply With Quote
Old 02-19-2014, 01:32 PM   #26
Velocity
Back to Retired.
Resident Owl
AdministratorRetired StaffDeveloperFFR Simfile Author
 
Velocity's Avatar
 
Join Date: Jul 2007
Posts: 1,751
Default Re: Website/database question

Quote:
Originally Posted by cedolad View Post
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.
Velocity is offline   Reply With Quote
Old 02-19-2014, 01:44 PM   #27
devonin
Very Grave Indeed
FFR Simfile AuthorFFR Veteran
 
devonin's Avatar
 
Join Date: Apr 2004
Location: Ontario, Canada
Age: 35
Posts: 10,094
Send a message via AIM to devonin Send a message via MSN to devonin
Default 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.
devonin is offline   Reply With Quote
Old 02-19-2014, 01:53 PM   #28
justin_ator
Washed Up
FFR Simfile AuthorFFR Veteran
 
justin_ator's Avatar
 
Join Date: Mar 2007
Location: Colorado Springs, CO
Posts: 7,380
Default 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?
__________________
justin_ator is offline   Reply With Quote
Old 02-19-2014, 02:14 PM   #29
Velocity
Back to Retired.
Resident Owl
AdministratorRetired StaffDeveloperFFR Simfile Author
 
Velocity's Avatar
 
Join Date: Jul 2007
Posts: 1,751
Default Re: Website/database question

Quote:
Originally Posted by devonin View Post
Just permanently nuke every account that hasn't logged in for the preceding two years. Should cut that number down subsantially.
Quote:
Originally Posted by justin_ator View Post
^

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.
Velocity is offline   Reply With Quote
Old 02-19-2014, 02:53 PM   #30
Untimely Friction
D6 Challeneged
FFR Veteran
 
Untimely Friction's Avatar
 
Join Date: Aug 2012
Age: 27
Posts: 1,221
Default 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.
Untimely Friction is offline   Reply With Quote
Old 02-19-2014, 08:02 PM   #31
choof
youtu.be/sM5hlrkaPSo
FFR Simfile AuthorD7 Elite KeysmasherFFR Veteran
 
choof's Avatar
 
Join Date: Nov 2013
Posts: 7,767
Default Re: Website/database question

Quote:
Originally Posted by Velocity View Post
This breaks thing.

>implying
choof is offline   Reply With Quote
Old 02-19-2014, 11:00 PM   #32
Velocity
Back to Retired.
Resident Owl
AdministratorRetired StaffDeveloperFFR Simfile Author
 
Velocity's Avatar
 
Join Date: Jul 2007
Posts: 1,751
Default Re: Website/database question

Quote:
Originally Posted by choof View Post
>implying
I've deleted my account before to see what happens.
Velocity is offline   Reply With Quote
Old 02-19-2014, 11:09 PM   #33
leonid
I am leonid
FFR Simfile AuthorFFR Music ProducerD7 Elite KeysmasherFFR Veteran
 
leonid's Avatar
 
Join Date: Oct 2008
Location: MOUNTAIN VIEW
Age: 30
Posts: 8,071
Default Re: Website/database question

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



Proud member of Team No
leonid is offline   Reply With Quote
Old 02-19-2014, 11:25 PM   #34
Izzy
Frau Bow
FFR Simfile AuthorFFR Veteran
 
Izzy's Avatar
 
Join Date: Jan 2003
Location: Kansas
Age: 29
Posts: 9,197
Send a message via AIM to Izzy Send a message via MSN to Izzy Send a message via Skype™ to Izzy
Default Re: Website/database question

8 years of inactivity then.
__________________
Join SMO IRC. irc.rizon.net#smo
Izzy is offline   Reply With Quote
Old 02-20-2014, 01:03 AM   #35
Crazyjayde
FFR Veteran
Wiki Administrator
FFR Wiki StaffFFR Veteran
 
Crazyjayde's Avatar
 
Join Date: May 2007
Location: Quebec
Posts: 1,156
Default Re: Website/database question

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.
Crazyjayde is offline   Reply With Quote
Old 02-20-2014, 08:13 AM   #36
choof
youtu.be/sM5hlrkaPSo
FFR Simfile AuthorD7 Elite KeysmasherFFR Veteran
 
choof's Avatar
 
Join Date: Nov 2013
Posts: 7,767
Default Re: Website/database question

Quote:
Originally Posted by Velocity View Post
I've deleted my account before to see what happens.
i meant implying that there isn't stuff on ffr that's broken regardless
choof is offline   Reply With Quote
Old 02-20-2014, 08:26 AM   #37
XelNya
Boomer
FFR Simfile AuthorFFR Veteran
 
XelNya's Avatar
 
Join Date: Sep 2012
Posts: 2,711
Default Re: Website/database question

Quote:
Originally Posted by Velocity View Post
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.
XelNya is offline   Reply With Quote
Old 02-20-2014, 09:58 AM   #38
Untimely Friction
D6 Challeneged
FFR Veteran
 
Untimely Friction's Avatar
 
Join Date: Aug 2012
Age: 27
Posts: 1,221
Default Re: Website/database question

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
Untimely Friction is offline   Reply With Quote
Old 02-20-2014, 10:02 AM   #39
Izzy
Frau Bow
FFR Simfile AuthorFFR Veteran
 
Izzy's Avatar
 
Join Date: Jan 2003
Location: Kansas
Age: 29
Posts: 9,197
Send a message via AIM to Izzy Send a message via MSN to Izzy Send a message via Skype™ to Izzy
Default Re: Website/database question

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.
__________________
Join SMO IRC. irc.rizon.net#smo
Izzy is offline   Reply With Quote
Old 02-20-2014, 10:09 AM   #40
Untimely Friction
D6 Challeneged
FFR Veteran
 
Untimely Friction's Avatar
 
Join Date: Aug 2012
Age: 27
Posts: 1,221
Default Re: Website/database question

Quote:
Originally Posted by Izzy View Post
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...
Untimely Friction is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are Off
[IMG] code is On
HTML code is Off

Forum Jump



All times are GMT -5. The time now is 11:27 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright FlashFlashRevolution