SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

adamarama posted:

I apologise if this isn't the right place to ask, I was wondering if anyone knows of any good resources (books or online) for learning SQL? There's so many books and sites available, I'm not sure which would be best for a beginner. I have never done programming before, I'm just learning as I hear it's quite useful in business analysis. Should I focus on mastering SELECT, or is it useful knowing how to construct databases?

Well i would start with select that's the most basic function your going to always use.

You can use something like a visual gui tool to create ones and certain ones even show you the sql they are going to run to do what you did with the gui so you can learn those kinds of things.

# ? Nov 23, 2009 20:10

Adbot: ADBOT LOVES YOU

# ? May 29, 2024 15:52

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

adamarama posted:

I have never done programming before, I'm just learning as I hear it's quite useful in business analysis. Should I focus on mastering SELECT, or is it useful knowing how to construct databases?

Obviously it would be great to just know everything, but if you're a beginner with no technical experience, constructing/managing/designing a database should be pretty low on your list. There's a wealth of knowledge to acquire as an analyst, based solely on the SELECT statement (basic selects, aggregations, joins, and analytics). You can get pretty far on SELECT material and not know anything about how databases should be designed/managed.

_{Technical note: If anything, normalized tables make data access more cumbersome. All it does is help maintain integrity free of errors.}

# ? Nov 23, 2009 20:17

Squashy Nipples: Aug 18, 2007

Eh, I dunno, without some relational-data basics, SQL doesn't always make sense. I do think that starting out requires some basic big-picture stuff before you dive into joining tables.

adamarama posted:

I have never done programming before, I'm just learning as I hear it's quite useful in business analysis.

Yes, quite useful. I'm a business analyst, and being able to write my own SQL has greatly expanded my toolset, and made me much more valuable to my company.

# ? Nov 23, 2009 21:14

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

There's a world of difference between "this is a two dimensional structure with optional values" and "this table isn't indexed properly and doesn't obey fourth normal form". I'm saying, to be an analyst, you probably don't need the latter.

What consumer would care about relational integrity? Even if you had two badly designed tables with duplicate data, it's not really your place to do anything about it (unless you are the data architect).

# ? Nov 23, 2009 21:44

Gallipoli: Apr 27, 2004; Not the Italian city. No one cares about the Italian city.

Jethro posted:

If you're using SQL Server 2005 or newer http://msdn.microsoft.com/en-us/library/ms190936%28SQL.90%29.aspx

No such luck, 2000. But I've found XMLSpy does basically exactly what I want.

# ? Nov 24, 2009 00:56

every: Apr 24, 2008

Hey, I have a really stupid question. I'm updating a database for someone, and I have very limited experience. I really can't gently caress this up, or bad things will happen.

I have a table called "property" and an enum field called "parking". I want to change all the values of this field to "Yes, fee".

so,

UPDATE property SET parking=Yes, fee;

or something like that??

# ? Nov 30, 2009 19:01

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

growing posted:

Hey, I have a really stupid question. I'm updating a database for someone, and I have very limited experience. I really can't gently caress this up, or bad things will happen.

I have a table called "property" and an enum field called "parking". I want to change all the values of this field to "Yes, fee".

so,

UPDATE property SET parking=Yes, fee;

or something like that??

well it would be

UPDATE property SET parking='Yes, fee';

but since its an enum field it does have the option of being a 'Yes, fee' in the descriptor right otherwise it will error saying you can't.

# ? Nov 30, 2009 19:03

every: Apr 24, 2008

Perfect, thanks.

Whoops, I forgot the ' '. Yes, it had the option of "Yes, fee." Someone set this database up a long time ago, and it has tons and tons of data...being an SQL newbie, I didn't want to mess anything up. Thanks for the double-check. Apologies for the lvl1 question.

every fucked around with this message at 19:28 on Nov 30, 2009

# ? Nov 30, 2009 19:09

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

growing posted:

Hey, I have a really stupid question. I'm updating a database for someone, and I have very limited experience. I really can't gently caress this up, or bad things will happen.

But surely backups of the data exist, right?

# ? Nov 30, 2009 21:06

Squashy Nipples: Aug 18, 2007

Hammerite posted:

But surely backups of the data exist, right?

Actually, that depends on how much I've pissed off the DBA...

# ? Nov 30, 2009 21:08

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Is it possible to gain programmatic access to the actual cost of a query, estimated or actual?

# ? Dec 2, 2009 20:28

var1ety: Jul 26, 2004

Triple Tech posted:

Is it possible to gain programmatic access to the actual cost of a query, estimated or actual?

Not sure what database you're asking about, but Oracle exposes estimated costs of cached cursors in the V$SQL_PLAN object.

# ? Dec 2, 2009 20:57

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Using MySQL, I'm creating a temp table:

code:

CREATE TEMPORARY TABLE test (
	Id VARCHAR(18) NOT NULL,
	SomeField VARCHAR(1300),
	SomeOtherField VARCHAR(20),
	YetAnotherField VARCHAR(100)
	PRIMARY KEY (Id, SomeOtherField),
	INDEX (YetAnotherField)
)

With the default my.ini, it takes 2 minutes to load 50,000 rows into this account (using prepared statement w/ batch). If I change my create statement to specify ENGINE = MEMORY and set tmp_table_size and max_heap_table_size both to 256M, it takes 5 seconds to load the same data.

Unfortunately, I don't have enough memory on this machine to have temp tables in memory that are that big for X number of users. Are there other ways I can speed this up? 2 minutes to load 50,000 rows seems like way too long.

edit: specifying ENGINE = MyISAM, overriding the InnoDB default, yields a 10 second load time. Hurrah!

fletcher fucked around with this message at 00:04 on Dec 3, 2009

# ? Dec 2, 2009 23:04

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Take off the index. If you want to speed up writes, drop all indices. Then if you need the index for reporting purposes, recreate it.

# ? Dec 2, 2009 23:26

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

i can't even imagine why you would put a 1300 character field into the primary key that doesn't make any sense.

# ? Dec 3, 2009 00:00

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Triple Tech posted:

Take off the index. If you want to speed up writes, drop all indices. Then if you need the index for reporting purposes, recreate it.

Interesting...so it would be faster to not have the indicies in the CREATE, and to just create them one I'm done inserting?

Sprawl posted:

i can't even imagine why you would put a 1300 character field into the primary key that doesn't make any sense.

Whoops, that should really be SomeOtherField, not the long one.

# ? Dec 3, 2009 00:02

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

fletcher posted:

Interesting...so it would be faster to not have the indicies in the CREATE, and to just create them one I'm done inserting?

Whoops, that should really be SomeOtherField, not the long one.

Yes inserts go much faster without keys and you can add them back much faster after you've imported all the rows.

# ? Dec 3, 2009 00:06

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

fletcher posted:

Interesting...so it would be faster to not have the indicies in the CREATE, and to just create them one I'm done inserting?

One of two things is happening, but they're both based on the same principle, just one worse than the other.

If you're inserting a stream of rows into a table (as singular inserts), each insert has to go through the table's indices, make a spot for itself, and then plop it in place. Multiply this by how many times you do it, seek put seek put, and accounting for the fact that the table gets longer and longer the more rows you have.

The other way is that you're doing a bulk insert. The database will sort your temporary data set, set down multiple markers in the real table, make room for your new rows, and then slide them in. Slightly faster, but incurs the cost of sorting and more temporary/logging space.

If you relieve the table of its indices, the database doesn't have to do any of this work. It can just very quickly append your rows, in any order, probably its nature convenient order, to the end of the table. Then after the rows are committed, you can go back and optimize for speed, asking the table to reshuffle all the data, but just sorting that entire set once.

A rule of thumb is... Anything you do to improve write/read performance will cause read/write performance to suffer. Adding indices to improve read performance will cause writes to be slower, because they will continually try to maintain these indices.

This isn't to say that you should always drop and recreate your indices (this also has a time cost, obviously, during re-creation). It's just that in some use cases, people need to optimize for very large, bulk inserts and dropping the indices and recreating them is part of this workflow. For single time inserts, it is negligable.

# ? Dec 3, 2009 06:15

Lamb-Blaster 4000: Sep 20, 2007

I need to be able to order the results of a mysql query in an arbitrary way

the query is

code:

select firstName,lastName,image from authors where id in (95,194,97,52,62,20,17,1)

and I need the results ordered the same way the list of ids is.

PS Triple Tech your av/text is AWESOME.

# ? Dec 3, 2009 17:21

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Does that list of IDs have any sort significance on some dimension inherent to them? Or is it truly, truly arbitrary?

Edit: The point is if there is, do a join and sort on that field.

PS, more great Gleevatag combos found in the Glee thread...

Triple Tech fucked around with this message at 18:26 on Dec 3, 2009

# ? Dec 3, 2009 17:27

Sneaking Mission: Nov 11, 2008

Lamb-Blaster 4000 posted:

I need to be able to order the results of a mysql query in an arbitrary way and I need the results ordered the same way the list of ids is.

Probably easiest way is to order by a decode statement

code:

select firstName,lastName,image from authors
where id in (95,194,97,52,62,20,17,1)
order by decode(id, 95,1, 194,2, 97,3, 52,4, ...)

Edit: Oh, looks like MySQL doesn't have decode statements.

In that case maybe case would work

code:

order by case id
  when 95 then 1
  when 194 then 2
  ...
end

Sneaking Mission fucked around with this message at 17:51 on Dec 3, 2009

# ? Dec 3, 2009 17:41

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Lamb-Blaster 4000 posted:

I need to be able to order the results of a mysql query in an arbitrary way

the query is
code:
select firstName,lastName,image from authors where id in (95,194,97,52,62,20,17,1)
and I need the results ordered the same way the list of ids is.

PS Triple Tech your av/text is AWESOME.

Well there are some issues when doing this.

select firstName,lastName,image from authors where id in (95,194,97,52,62,20,17,1) order by id=1,id=17,id=20,id=62,id=52,id=97,id=194,id=95

no idea why its work in reverse like this but i can show you this.

select id,description from area where id in (1,22,5,10,20,27,32) order by id=32,id=27,id=20,id=10,id=5,id=22,id=1

code:

1	N Island
22	North ON
5	Howe Sound
10	Yukon
20	NW ONT
27	NB
32	MARITIMES

# ? Dec 3, 2009 18:24

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Sprawl posted:

no idea why its work in reverse like this but i can show you this.

select id,description from area where id in (1,22,5,10,20,27,32) order by id=32,id=27,id=20,id=10,id=5,id=22,id=1
code:
1	N Island
22	North ON
5	Howe Sound
10	Yukon
20	NW ONT
27	NB
32	MARITIMES

You can see why it's in reverse by going through one by one. First comparison that happens is: is id=32? For MARITIMES only it is, so id=32 evaluates as 1 for MARITIMES and 0 for all the others. So since default order is ascending, MARITIMES goes to the end of the list. etc.

You have a couple of ways to get them in the other order; use <> instead of =, or add DESC after each comparison.

# ? Dec 3, 2009 18:58

Lamb-Blaster 4000: Sep 20, 2007

the idea is there's a table of authors and at any point in time I should be able to change which authors are pulled together and in what order just by swapping the ids.

Sprawl, your solution worked, thanks!

Edit: also thanks hammerite for the reverse solution.

Lamb-Blaster 4000 fucked around with this message at 19:02 on Dec 3, 2009

# ? Dec 3, 2009 18:59

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Hammerite posted:

You can see why it's in reverse by going through one by one. First comparison that happens is: is id=32? For MARITIMES only it is, so id=32 evaluates as 1 for MARITIMES and 0 for all the others. So since default order is ascending, MARITIMES goes to the end of the list. etc.

You have a couple of ways to get them in the other order; use <> instead of =, or add DESC after each comparison.

That didn't really occur to me that does make sense.

# ? Dec 3, 2009 19:29

MoNsTeR: Jun 29, 2002

var1ety posted:

Not sure what database you're asking about, but Oracle exposes estimated costs of cached cursors in the V$SQL_PLAN object.

I would recommend against actually using this cost value for anything. If I had a nickel for every cost=1 query that took 12 hours to run I could retire right now.

# ? Dec 4, 2009 16:58

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Well it's just sort of an abstract/uncommon question... Like if a business were to charge users for the queries they write, what metric would you use to charge them? So I figured query cost.

# ? Dec 4, 2009 17:11

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Triple Tech posted:

Well it's just sort of an abstract/uncommon question... Like if a business were to charge users for the queries they write, what metric would you use to charge them? So I figured query cost.

Well I don't know about other database systems, but when you ask MySQL to EXPLAIN SELECT ... it gives you a bunch of information that you can use to infer the approximate "cost" of the query. Like it will tell you how many rows it thinks it needs to examine in each joined table, whether it could use any indices, whether it has to use a filesort to get things in the correct order, etc. Details here. Maybe you can get your DBMS's query optimiser to record its conclusions in this way in a log file, for queries that it runs. Doesn't any such endeavour come with a certain amount of overhead anyway, though, if you're doing that for every query?

Also, don't most DBMSs have an option to log "slow" queries? You could just examine this log and have the user responsible accrue a cost.

When you say "the queries they write", also, surely the cost of a given query scales with the size of the table. So it's more the queries the users execute than the ones they write.

# ? Dec 4, 2009 17:32

MoNsTeR: Jun 29, 2002

I would be inclined to use runtime, simply because it's objective and it automatically scales with system load giving you easy Peak Load Pricing.

If you didn't want price to scale that way, you could, assuming again Oracle, run all queries with tracing enabled and charge based on "db block gets", "consistent gets", or "physical reads", possibly with a surcharge for additional sort operations.

# ? Dec 4, 2009 23:28

atb: Mar 20, 2009

Having trouble with what is probably a pretty simple problem related to requesting and using a URL from mysql.

Basically I have some RSS urls in a mysql database I want to use in some code (SimplePie). I am querying the database to get the urls, and that works fine, but when I try to use the urls it seems to be omitting the http: portion and tries to look locally for the rss feed. The error I'm getting is:

Warning: file_get_contents(//www.cbssports.com/nfl/teams/syndicate/ARI',) [function.file-get-contents]: failed to open stream: No such file or directory

In the database the url is formatted as 'http://www.cbssports.com/nfl/teams/syndicate/ARI'

The code I'm using to use the url is:

$query = "SELECT rssfeeds.URL FROM `rssfeeds` WHERE rssfeeds.TeamID='arizonacardinals'";
$result = mysql_query($query);
for ($j=0; $row=mysql_fetch_array($result);$j++)
$feeds = new SimplePie (array ("{$row['URL']}"));

I'm sure there's a simple solution to get it to use the full url, but new to php and mysql. Also the code works fine when I put that url in the $feeds array as 'http://www.cbssports.com/nfl/teams/syndicate/ARI' rather than calling it from the database.

# ? Dec 5, 2009 08:12

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

What character set is the field that holds it?

# ? Dec 5, 2009 09:03

atb: Mar 20, 2009

Sprawl posted:

What character set is the field that holds it?

Currently have it as VARCHAR

# ? Dec 5, 2009 18:19

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

atb posted:

Currently have it as VARCHAR

That's the column type. Columns that have a string data type (CHAR, VARCHAR or TEXT) also have a character set and collation. If you issue SHOW CREATE TABLE `rssfeeds` then you will get a CREATE TABLE statement for the table; at the end of the statement there should be a clause that says something like CHARACTER SET utf8 or CHARACTER SET latin1. Tell us what this clause says, unless there is a similar clause after the column listing for `URL`, in which case tell us what that clause says instead.

# ? Dec 5, 2009 19:43

atb: Mar 20, 2009

CREATE TABLE `rssfeeds` (
`TeamID` varchar(150) NOT NULL,
`URL` varchar(255) character set dec8 collate dec8_bin NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

edit: Tried changing the URL collate to ut8_general_ci and latin1_swedish_ci, neither which helped

Also, if I try echo $row["URL"]; I get the url printed as 'http://www.cbssports.com/nfl/teams/syndicate/ARI' which is what matches the sql data.

atb fucked around with this message at 22:29 on Dec 5, 2009

# ? Dec 5, 2009 20:39

atb: Mar 20, 2009

Figured out the problem, changed the collation to latin1_swedish_ci and the field type to Text. Have the URL in sql as http://www.cbssports.com/nfl/teams/syndicate/ARI without any quotations.

Now the problem is in order to process more than one feed I need to have a comma after each feed. If I put a comma behind the url in sql, it doesn't process the feed.

Is there a way I can add a comma into the code pulling the feeds to that it adds it onto the end of the url being processed? The code I have is:

$query = "SELECT rssfeeds.URL FROM `rssfeeds` WHERE rssfeeds.TeamID='arizonacardinals'";
$result = mysql_query($query);
for ($j=0; $row=mysql_fetch_array($result);$j++)
$feeds = new SimplePie (array ($row['URL']));

# ? Dec 5, 2009 23:06

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

atb posted:

Figured out the problem, changed the collation to latin1_swedish_ci and the field type to Text. Have the URL in sql as http://www.cbssports.com/nfl/teams/syndicate/ARI without any quotations.

Now the problem is in order to process more than one feed I need to have a comma after each feed. If I put a comma behind the url in sql, it doesn't process the feed.

Is there a way I can add a comma into the code pulling the feeds to that it adds it onto the end of the url being processed? The code I have is:

$query = "SELECT rssfeeds.URL FROM `rssfeeds` WHERE rssfeeds.TeamID='arizonacardinals'";
$result = mysql_query($query);
for ($j=0; $row=mysql_fetch_array($result);$j++)
$feeds = new SimplePie (array ($row['URL']));

I think what your saying is you want to add a "," to the end of the url after the sql extraction during the php? I think right?

# ? Dec 6, 2009 02:03

atb: Mar 20, 2009

Yeah, right now if I extract the output is http://www.url.com http://www.url2.com http://www.url3.com

So I need to somehow get a "," after each URL.

# ? Dec 6, 2009 02:46

Kekekela: Oct 28, 2004

atb posted:

Yeah, right now if I extract the output is http://www.url.com http://www.url2.com http://www.url3.com

So I need to somehow get a "," after each URL.

Can you just do it in your select statement like:
"SELECT rssfeeds.URL + ',' FROM `rssfeeds` WHERE rssfeeds.TeamID='arizonacardinals'";

(I see you said that you can't add the , in sql but wasn't sure if you just meant adding it directly to the data)

If that is too early in the routine, it looks like you would add it here:
$feeds = new SimplePie (array ($row['URL']+','));
But I don't know php so I'm just kind of guessing what that code is doing.

Kekekela fucked around with this message at 06:21 on Dec 6, 2009

# ? Dec 6, 2009 06:13

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

atb posted:

Yeah, right now if I extract the output is http://www.url.com http://www.url2.com http://www.url3.com

So I need to somehow get a "," after each URL.

To me this says that you ought at some point to have an array of URLs that you want to convert into a comma-separated string. So the solution should just be

code:

$mystring = implode(', ',$myarray);

but that said, I don't really know how you're constructing your string of URLs.

Kekekela posted:

If that is too early in the routine, it looks like you would add it here:
$feeds = new SimplePie (array ($row['URL']+','));

The concatenation operator in PHP is a full stop, so it would be $row['URL'].',' (I haven't checked, but I don't think + would be legal).

# ? Dec 6, 2009 16:12

Adbot: ADBOT LOVES YOU

# ? May 29, 2024 15:52

Vince McMahon: Dec 18, 2003

could try GROUP_CONCAT but that wouldn't put a comma after the last one

# ? Dec 6, 2009 22:18

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »