Hammerite posted:

I feel like it should be a rule for this thread that if you ask a question, you should state what RDBMS you are using. They vary so much in some aspects that it's frequently crucial information.

Hadlock posted:

Ho many GB should I plan on dedicating to SQL Server 2014 VM that handles a 4GB database with say, 4 simple queries a second?

Agrikk posted:

I have what I think amounts to a data warehousing question for SQL Server 2012 and I'm not sure how to approach it.

Agrikk posted:

I have what I think amounts to a data warehousing question for SQL Server 2012 and I'm not sure how to approach it.

I have a table consisting of user data from Folding@home that I have been capturing once an hour for the last year.

Table UserData has the structure:

code:

TimeStamp             UserName  Score      WorkUnits  TeamNumber
2014-04-29 12:34:56   Agrikk    33219802   20824      33

So basically, UserData contains an hourly snapshot for each of 1.5 million Folding@Home users for the last year.

There is one primary key on (TimeStamp, UserName, TEamNumber).

What I'm trying to figure out is how to build the underlying data structures will be accessed by a PHP web server to produce a page like this one that produces production data by user.

If I create a summary table that contains a single data point per user per day, I still have hundreds of millions of rows of data that need to be parsed to create a graph, and I just don't understand how to build a database that can return a query at web-browsing speeds.

I mean, the referenced page returns a query in less than a second, and I just don't understand how that is possible.

fletcher posted:

Does it have to generate it in real time on demand? Maybe just generate the summary report periodically with a cron job and serve up the generated report.

FeloniousDrunk posted:

Using MySQL 5.5. bibdata is TEXT, others are VARCHAR or BIGINT as appropriate. All the tables are InnoDB. I already tried breaking bibdata out into a MyISAM table with fulltext indexing and using MATCH, but that actually turned out to be slower. I suppose what's killing it is the leading '%'s in the LIKEs, but I can't think of how to avoid that.

krnhotwings posted:

As far as I understand it, joining on the separate indexed table for bibdata would be slower because you'd be doing an indexed filter on top of a full table scan rather than just doing the full table scan outright.

You could convert the items table to MyISAM with fulltext indexes and searching on bibdata, title, and author:

code:

SELECT 
  I.`item_id`,
  I.`title`,
  I.`author`,
  I.`bibdata`,
  I.`callnumber`,
  I.`uri`,
  I.`hash`,
  I.`physical_format`,  
  GROUP_CONCAT(C.`course_id`, '\x1f', C.`lmsid` SEPARATOR '\x1e') AS lmsids 
FROM 
  `item` I 
  JOIN `course_item` CI USING(`item_id`) 
  JOIN `course` C USING(`course_id`) 
WHERE MATCH(I.title, I.bibdata, I.author) AGAINST('searchterm')
GROUP BY 
  I.`item_id`

Any reason you're not using MyISAM? 'cause I think you're SOL otherwise. If you're trying to avoid MyISAM, you could upgrade to 5.6 (5.6 has fulltext indexes and searching for InnoDB)

Edit: Earlier, I posted a pretty horrible suggestion, which I think is best if it's nuked away (it involved union selects for each LIKE and some other whacked poo poo.)

FeloniousDrunk posted:

Sneaky, I was going to reply to your previous post. InnoDB for transactions, which is kind of a necessity. The DB server is running Ubuntu 12.04.4, MySQL 5.5 is as high as that goes at the moment (I suppose ever, now that 14.04 is out) and I'd rather not compile my own. I did consider it, but the app I'm building is being considered for sharing with other places and that's not something I'd want to support. Maybe once 5.6 gets into standard repos.

Fortunately the query is still less than a second and it's only relevant in the administrative functions, not the student-facing part.

Agrikk posted:

I have what I think amounts to a data warehousing question for SQL Server 2012 and I'm not sure how to approach it.

I have a table consisting of user data from Folding@home that I have been capturing once an hour for the last year.

Table UserData has the structure:

code:

TimeStamp             UserName  Score      WorkUnits  TeamNumber
2014-04-29 12:34:56   Agrikk    33219802   20824      33

So basically, UserData contains an hourly snapshot for each of 1.5 million Folding@Home users for the last year.

There is one primary key on (TimeStamp, UserName, TEamNumber).

What I'm trying to figure out is how to build the underlying data structures will be accessed by a PHP web server to produce a page like this one that produces production data by user.

If I create a summary table that contains a single data point per user per day, I still have hundreds of millions of rows of data that need to be parsed to create a graph, and I just don't understand how to build a database that can return a query at web-browsing speeds.

I mean, the referenced page returns a query in less than a second, and I just don't understand how that is possible.

Agrikk posted:

Any help with this?

DreadCthulhu posted:

Postgres: One of my tables has gotten to 20mil rows and is adding them up at a rate of about 2mil/week. The rate is accelerating. Lookups are still blazing fast for the most part since it's still fitting into memory, and the typical query by primary key serial id will return 200-500 rows in no time.

I hear however that even for an add-only table things are about to get pretty lovely. Vacuuming (which right now autovacuum never does since I haven't rolled over the transaction counter and it's add-only) might start taking a week on a 500mil rows table etc.

I'm told that date-based partitioning is something worth looking into, and it fits my use-case pretty reasonably. Any other recommendations for once you get big-boy tables?

DreadCthulhu posted:

Postgres: One of my tables has gotten to 20mil rows and is adding them up at a rate of about 2mil/week. The rate is accelerating. Lookups are still blazing fast for the most part since it's still fitting into memory, and the typical query by primary key serial id will return 200-500 rows in no time.

I hear however that even for an add-only table things are about to get pretty lovely. Vacuuming (which right now autovacuum never does since I haven't rolled over the transaction counter and it's add-only) might start taking a week on a 500mil rows table etc.

I'm told that date-based partitioning is something worth looking into, and it fits my use-case pretty reasonably. Any other recommendations for once you get big-boy tables?

Cast_No_Shadow posted:

Due to reasons I've got ~4 weeks to learn as much as humanly possible from a DB point of view (inside a corporate environment), both design and implimentation.

I currently do dev work that has little to do with DB's beyond writing the odd SQL statement to pull\edit\insert some data.

Assume I know nothing beyond very basic Joins, SQL statements and what a relational database is.

Can anyone give me a list of things to read\resources that are helpful.

fletcher posted:

Something like this maybe?

code:

SELECT SUBSTRING('Stone Brewing Company', 1, LOCATE('brew', 'Stone Brewing Company')-1);

fletcher posted:

Something like this maybe?

code:

SELECT SUBSTRING('Stone Brewing Company', 1, LOCATE('brew', 'Stone Brewing Company')-1);

Cast_No_Shadow posted:

Due to reasons I've got ~4 weeks to learn as much as humanly possible from a DB point of view (inside a corporate environment), both design and implimentation.

I currently do dev work that has little to do with DB's beyond writing the odd SQL statement to pull\edit\insert some data.

Assume I know nothing beyond very basic Joins, SQL statements and what a relational database is.

Can anyone give me a list of things to read\resources that are helpful.

Sir Bobert Fishbone posted:

or try this in access:

code:

SELECT breweries.[name], MID(breweries.[name], 1, INSTR(1, breweries.[name], 'brew')-1)
FROM breweries;

raej posted:

Everyone is going to rag on me for using Access for this (it's a csv import that needs cleaning up and it's quick and dirty)

But I'm getting errors with:

code:

SELECT breweries.[name], SUBSTRING(breweries.[name], 1, LOCATE(' brew', breweries.[name])-1)
FROM breweries;

raej posted:

Checking out Open Refine. The sheer amount of data may make this difficult though. Breweries has over 14k entries, and Beers has over 185k entries. Doing anything by hand is not an option.

Janitor Prime posted:

raej posted:

Yes! Thank you!

Oh great SQL guru, can you help me with this last step then?

Two tables: Beers, Breweries (Simplified for the example)

Breweries
id,name

Beers
id,name,breweryid

A lot of the beers list a partial of the brewery name in the beer name. I want that removed.

code:

breweries

id|name                           
------------------------------
 1|Stone Brewing Company
 2|Stone City Brewing Company
 3|Peoples Pint
 4|Boston Beer Company

code:

beers

id|name                          |breweryid
-------------------------------------------
 1|Stone Smoked Porter           |1
 2|Stone City Iowa Pale Ale      |2
 3|Peoples Pint Brakeshoe Porter |3
 4|Sam Adams Blueberry Hill Lager|4

So what I'm seeing now when I combine a beer with its brewery are things like "Stone Brewing Company Stone Smoked Porter" which is a bit cumbersome.

Solution, remove the stripped down Brewery Name from the Beer name. The above SQL will get the stripped down Brewery Name, and I'm thinking there's a reverse part that will be able to get the stripped-brewery-name-removed-beer-name.

Logically, it would be something like:
For each beer, select everything after stripped down brewery name for this beer's brewery

DreadCthulhu posted:

Postgres: One of my tables has gotten to 20mil rows and is adding them up at a rate of about 2mil/week. The rate is accelerating. Lookups are still blazing fast for the most part since it's still fitting into memory, and the typical query by primary key serial id will return 200-500 rows in no time.

I hear however that even for an add-only table things are about to get pretty lovely. Vacuuming (which right now autovacuum never does since I haven't rolled over the transaction counter and it's add-only) might start taking a week on a 500mil rows table etc.

I'm told that date-based partitioning is something worth looking into, and it fits my use-case pretty reasonably. Any other recommendations for once you get big-boy tables?

Xae posted:

1) Have a good Indexing Strategy
2) Have a good Querying Strategy
3) Don't gently caress with settings unless you have to

Sir Bobert Fishbone posted:

I don't have Access in front of me, but could you try something like

code:

SELECT breweries.[name], MID(breweries.[name], 1, INSTR(1, breweries.[name], 'brew')-1), MID(beers.[name], LEN(MID(breweries.[name], 1, INSTR(1, breweries.[name], 'brew')-1)), LEN(beers.name])
FROM beers INNER JOIN breweries on breweries.id = beers.breweryid;

?

Erwin posted:

Do you have the correct indexes?

fankey posted:

Database 101 question. What would be a good way to organize a music library database ( SQLite ) assuming each song had (file,genre,artist,album,track,year). I've started reading up on databases and apparently it's important to normalize your database or it will all collapse in a fiery ball. I'm wondering how far to take things. It seems like I would want to have a (genre_id,genre) table and possibly a (artist_id,artist) one as well? That would make the main table (file,genre_id,artist_id,album,track,year)? Is there a reason to go crazy and also have a (album_id,album) table as well?

I won't be editing the database from my application if that matters.

KARMA! posted:

Yep, you want an albums table as well, so this gives you the most flexibility when you want to filter the data in any way.