02-18-2014, 09:42 PM | #21 | ||
nanodesu~
|
Re: Website/database question
Quote:
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:
__________________
FMO AAAs (1): Within Life :: FGO AAAs (1): Einstein-Rosen Bridge |
||
02-19-2014, 07:08 AM | #22 |
x'); DROP TABLE FFR;--
Join Date: Nov 2010
Posts: 6,332
|
Re: Website/database question
Is there a downside to just indexing everything?
|
02-19-2014, 07:25 AM | #23 |
moonchild~
|
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 ._.
__________________
|
02-19-2014, 07:44 AM | #24 |
the Mathemagician~
|
Re: Website/database question
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. |
02-19-2014, 09:13 AM | #25 |
Snek
Join Date: Jan 2003
Location: Kansas
Age: 34
Posts: 9,192
|
Re: Website/database question
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.
|
02-19-2014, 12:32 PM | #26 | |
Doing the wrong thing the right way since 2010.
Site and Game Administrator
Join Date: Jul 2007
Posts: 1,813
|
Re: Website/database question
Quote:
And 32 million score entries across every level table. |
|
02-19-2014, 12:44 PM | #27 |
Very Grave Indeed
|
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.
|
02-19-2014, 12:53 PM | #28 |
🥓<strong><span style="col
Resident Overseer
Join Date: Mar 2007
Location: Kingsport, TN
Posts: 7,648
|
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?
__________________
|
02-19-2014, 01:14 PM | #29 | |
Doing the wrong thing the right way since 2010.
Site and Game Administrator
Join Date: Jul 2007
Posts: 1,813
|
Re: Website/database question
Quote:
|
|
02-19-2014, 01:53 PM | #30 |
D6 Challeneged
Join Date: Aug 2012
Age: 31
Posts: 1,267
|
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.
|
02-19-2014, 07:02 PM | #31 |
Banned
Join Date: Nov 2013
Posts: 8,563
|
Re: Website/database question
|
02-19-2014, 10:00 PM | #32 |
Doing the wrong thing the right way since 2010.
Site and Game Administrator
Join Date: Jul 2007
Posts: 1,813
|
Re: Website/database question
|
02-19-2014, 10:09 PM | #33 |
I am leonid
Join Date: Oct 2008
Location: MOUNTAIN VIEW
Age: 35
Posts: 8,080
|
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.
|
02-19-2014, 10:25 PM | #34 |
Snek
Join Date: Jan 2003
Location: Kansas
Age: 34
Posts: 9,192
|
Re: Website/database question
8 years of inactivity then.
|
02-20-2014, 12:03 AM | #35 |
FFR Veteran
Wiki Administrator
Join Date: May 2007
Location: Quebec
Posts: 1,169
|
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. |
02-20-2014, 07:13 AM | #36 |
Banned
Join Date: Nov 2013
Posts: 8,563
|
Re: Website/database question
|
02-20-2014, 07:26 AM | #37 |
[Nobody liked that.]
Join Date: Sep 2012
Posts: 3,359
|
Re: Website/database question
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.
|
02-20-2014, 08:58 AM | #38 |
D6 Challeneged
Join Date: Aug 2012
Age: 31
Posts: 1,267
|
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 |
02-20-2014, 09:02 AM | #39 |
Snek
Join Date: Jan 2003
Location: Kansas
Age: 34
Posts: 9,192
|
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.
|
02-20-2014, 09:09 AM | #40 | |
D6 Challeneged
Join Date: Aug 2012
Age: 31
Posts: 1,267
|
Re: Website/database question
Quote:
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
|
|