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 »

Thel: Apr 28, 2010

Hi guys. I have a really stupid question.

Sample table (this is MS SQL server 2005):

code:

PhoneID PersonID  Type  AreaCode  Number  UpdatedDate
   1       1      Home     03    1111111  01-Jan-2009
   2       1      Home     03    1111112  01-Feb-2009
   3       1     Mobile   021     123456  01-Jan-2009
   4       1     Mobile   021     123457  01-Feb-2009
   5       2      Home     03    1111234  01-Jan-2009
   6       2     Mobile   021     456789  01-Jan-2009

How do I write a view to get the latest Phone entries - maximum of one home phone and one mobile phone per PersonID, with newest taking priority? (If it's easier, it can be split into a view per Type) i.e.:

code:

PhoneID PersonID  Type  AreaCode  Number  UpdatedDate
   2       1      Home     03    1111112  01-Feb-2009
   4       1     Mobile   021     123457  01-Feb-2009
   5       2      Home     03    1111234  01-Jan-2009
   6       2     Mobile   021     456789  01-Jan-2009

# ? Aug 23, 2010 04:03

Adbot: ADBOT LOVES YOU

# ? May 14, 2024 08:58

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Thel posted:

Hi guys. I have a really stupid question.

Sample table (this is MS SQL server 2005):
code:
PhoneID PersonID  Type  AreaCode  Number  UpdatedDate
   1       1      Home     03    1111111  01-Jan-2009
   2       1      Home     03    1111112  01-Feb-2009
   3       1     Mobile   021     123456  01-Jan-2009
   4       1     Mobile   021     123457  01-Feb-2009
   5       2      Home     03    1111234  01-Jan-2009
   6       2     Mobile   021     456789  01-Jan-2009
How do I write a view to get the latest Phone entries - maximum of one home phone and one mobile phone per PersonID, with newest taking priority? (If it's easier, it can be split into a view per Type) i.e.:
code:
PhoneID PersonID  Type  AreaCode  Number  UpdatedDate
   2       1      Home     03    1111112  01-Feb-2009
   4       1     Mobile   021     123457  01-Feb-2009
   5       2      Home     03    1111234  01-Jan-2009
   6       2     Mobile   021     456789  01-Jan-2009

It's not a stupid question. It's a question that gets asked over and over again, but the answer is non-obvious.

Fortunately, since you are using 2005, I can answer with my favorite thing ever, analytical/window functions:

code:

SELECT PhoneID
      ,PersonID
      ,Type
      ,AreaCode
      ,Number
      ,UpdateDate
  FROM (SELECT PhoneID
              ,PersonID
              ,Type
              ,AreaCode
              ,Number
              ,UpdateDate
              ,row_number() over (partition by PersonID, Type
                                  order by UpdateDate desc) ph_rnk

          FROM tablename) rnktbl
 WHERE ph_rnk = 1

Jethro fucked around with this message at 19:58 on Aug 23, 2010

# ? Aug 23, 2010 19:56

PuTTY riot: Nov 16, 2002

I'm trying to write a deployment script, and would like to be able to call sqlcmd to execute every query in a specified directory. I think the problem is that it's looking for a file instead of a directory

code:

::execute the changes to the database by calling the specified directory
for %%X in (*.SQL) do sqlcmd -S 10.0.1.13 -U username -P password -d db -i "C:\PATH\TO\SQLCMDTEST\" >DEPLOYLOG.txt 2>&1

Here's the error message I receive:

code:

Sqlcmd: Error: Error occurred while opening or operating on file C:\Users\\Desktop\SQLCMDTEST\ (Reason: The system cannot find the path specified).

PuTTY riot fucked around with this message at 05:52 on Oct 20, 2010

# ? Aug 24, 2010 17:53

slipped: Jul 12, 2001

Hey guys, is there a defacto book for MySQL for learning the ins and outs from both a developers perspective and a dba perspective?
I'm just starting at a new job and realized my SQL skills were pretty weak, especially with MySQL. Any advice is appreciated, I'm looking forward to spending some money on amazon

# ? Aug 24, 2010 20:22

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

For the last five years, I've been doing my standard joins like so:

code:

SELECT * 
FROM table1
LEFT JOIN table2 ON table1.id = table2.id
LEFT JOIN table3 ON table2.otherid = table3.otherid

It's been suggested I simply do...

code:

SELECT *
FROM table1, table2, table3
WHERE table1.id = table2.id
AND table2.otherid = table3.otherid

But based on my cursory research, this 'implied' join is functionally an inner join, rather than a left join. Is this correct? Though to be honest, with about 95% of my queries, the two would return the same results.

# ? Aug 24, 2010 20:39

Kekekela: Oct 28, 2004

Golbez posted:

But based on my cursory research, this 'implied' join is functionally an inner join, rather than a left join. Is this correct?

Yep.

# ? Aug 24, 2010 20:41

No Safe Word: Feb 26, 2005

Golbez posted:

For the last five years, I've been doing my standard joins like so:
code:
SELECT * 
FROM table1
LEFT JOIN table2 ON table1.id = table2.id
LEFT JOIN table3 ON table2.otherid = table3.otherid
It's been suggested I simply do...
code:
SELECT *
FROM table1, table2, table3
WHERE table1.id = table2.id
AND table2.otherid = table3.otherid
But based on my cursory research, this 'implied' join is functionally an inner join, rather than a left join. Is this correct? Though to be honest, with about 95% of my queries, the two would return the same results.

That's correct though the latter is less explicit and which is why I prefer the former.

# ? Aug 24, 2010 20:42

Mackerel, the Thief: Sep 24, 2003

Golbez posted:

It's been suggested I simply do...

It sounds like you work with nitpickers. I prefer writing out the joins just as you do as it's more legible, easier to understand, and easier to change.

# ? Aug 24, 2010 21:22

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

Mackerel, the Thief posted:

It sounds like you work with nitpickers. I prefer writing out the joins just as you do as it's more legible, easier to understand, and easier to change.

I agree that explicit join syntax is the way to go. Far more systematic (since afaik you need to use it in order to express anything other than an inner join), and easier to understand.

# ? Aug 24, 2010 21:36

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Mackerel, the Thief posted:

It sounds like you work with nitpickers. I prefer writing out the joins just as you do as it's more legible, easier to understand, and easier to change.

It's literally the first time I've ever worked with other coders, and I'm the new guy, so I'm very much inclined to do what they want to fit with their style. My hope is, in a few months, I can start hopefully influencing them instead of the other way around. I'd love to introduce this joint to objects and mysqli/PDO.

# ? Aug 24, 2010 21:57

Mackerel, the Thief: Sep 24, 2003

Golbez posted:

... I'd love to introduce this joint to objects ...

They... don't... use objects?! Like, at all? Everything procedural? :aaa:

# ? Aug 24, 2010 22:10

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Mackerel, the Thief posted:

They... don't... use objects?! Like, at all? Everything procedural?

# ? Aug 24, 2010 22:30

boak: Aug 17, 2010; im gay

Help me write a (query. I have two tables, one a listing of vote cards, the other a list of votes.

Each vote card can have one or more votes for a player. So for each entry in the votecard table, there are 1 or more entries in the votes table giving a certain amount of votes to a particular player.

I want a query that will return a list of votecards and the total number of votes given on that card.

Basic table data shown below:

code:

table: votecards

ID_CRD: primary key, the card id obviously
ID_FXT: foreign key to fixtures table. Each card is for a particular fixture (game)

+----------+----------+
| ID_CRD   | ID_FXT   |
+----------+----------+
| 00000046 | 00000704 |
| 00000047 | 00000704 |
| 00000048 | 00000704 |
| 00000037 | 00000709 |
| 00000038 | 00000709 |
| 00000039 | 00000709 |
| 00000028 | 00000711 |
| 00000029 | 00000711 |
| 00000030 | 00000711 |
| 00000022 | 00000716 |
+----------+----------+


table: votes

ID_CRD: FK to card table
ID_PLY: FK to player table
votes: how many votes were given to the player.

+----------+----------+-------+
| ID_CRD   | ID_PLY   | votes |
+----------+----------+-------+
| 00000001 | 00000565 |     2 |
| 00000001 | 00000138 |     2 |
| 00000001 | 00000094 |     1 |
| 00000001 | 00000233 |     1 |
| 00000001 | 00000230 |     1 |
| 00000002 | 00000138 |     1 |
| 00000002 | 00000565 |     1 |
| 00000002 | 00000230 |     1 |
| 00000002 | 00000094 |     1 |
| 00000002 | 00000233 |     1 |
+----------+----------+-------+

I've been fiddling around with a query that involves a subquery with SUM() on the votes table but I dont know what to use in the WHERE clause so that it will only SUM() the votes for the particular card:

code:

SELECT ID_CRD, ID_FXT,
(SELECT SUM(votes) FROM pt_votes WHERE ??????) as total_votes
FROM votecards
ORDER BY total_votes ASC

I have a feeling I'm going about it the wrong way though. Any ideas?

# ? Aug 25, 2010 06:40

boak: Aug 17, 2010; im gay

Figured it out. was going about it completely wrong.

Just needed a simply query with a group by statement -_-

# ? Aug 25, 2010 07:16

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!

boak posted:

Help me write a (query. I have two tables, one a listing of vote cards, the other a list of votes.

Each vote card can have one or more votes for a player. So for each entry in the votecard table, there are 1 or more entries in the votes table giving a certain amount of votes to a particular player.

I want a query that will return a list of votecards and the total number of votes given on that card.

Basic table data shown below:
code:
table: votecards

ID_CRD: primary key, the card id obviously
ID_FXT: foreign key to fixtures table. Each card is for a particular fixture (game)

+----------+----------+
| ID_CRD   | ID_FXT   |
+----------+----------+
| 00000046 | 00000704 |
| 00000047 | 00000704 |
| 00000048 | 00000704 |
| 00000037 | 00000709 |
| 00000038 | 00000709 |
| 00000039 | 00000709 |
| 00000028 | 00000711 |
| 00000029 | 00000711 |
| 00000030 | 00000711 |
| 00000022 | 00000716 |
+----------+----------+


table: votes

ID_CRD: FK to card table
ID_PLY: FK to player table
votes: how many votes were given to the player.

+----------+----------+-------+
| ID_CRD   | ID_PLY   | votes |
+----------+----------+-------+
| 00000001 | 00000565 |     2 |
| 00000001 | 00000138 |     2 |
| 00000001 | 00000094 |     1 |
| 00000001 | 00000233 |     1 |
| 00000001 | 00000230 |     1 |
| 00000002 | 00000138 |     1 |
| 00000002 | 00000565 |     1 |
| 00000002 | 00000230 |     1 |
| 00000002 | 00000094 |     1 |
| 00000002 | 00000233 |     1 |
+----------+----------+-------+
I've been fiddling around with a query that involves a subquery with SUM() on the votes table but I dont know what to use in the WHERE clause so that it will only SUM() the votes for the particular card:
code:
SELECT ID_CRD, ID_FXT,
(SELECT SUM(votes) FROM pt_votes WHERE ??????) as total_votes
FROM votecards
ORDER BY total_votes ASC
I have a feeling I'm going about it the wrong way though. Any ideas?

This looks like a simple problem.

code:

Select
t1.ID_CRD,
t1.ID_FXT,
sum(t2.votes) as votes
from votecards as t1
inner join votes as t2  on t1.ID_CRD = t2.ID_CRD
group by t1.ID_CRD
order by sum(t2.votes) ASC

Sprawl fucked around with this message at 07:43 on Aug 25, 2010

# ? Aug 25, 2010 07:20

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

In a situation where transactional integrity isn't required (I'm not updating bank records here), is there any reason at all to be fancy and go with a multiple-table delete statement in MySQL over the much more understandable and sane method of using two individual delete statements?

code:

DELETE lists
      ,list_contacts
  FROM lists
      ,list_contacts
 WHERE lists.list_id = 15
   AND list_contacts.list_id = 15;

versus

code:

DELETE FROM lists WHERE list_id = 15;
DELETE FROM list_contacts WHERE list_id = 15;

# ? Aug 25, 2010 23:04

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

Golbez posted:

In a situation where transactional integrity isn't required (I'm not updating bank records here), is there any reason at all to be fancy and go with a multiple-table delete statement in MySQL over the much more understandable and sane method of using two individual delete statements?
code:
DELETE lists
      ,list_contacts
  FROM lists
      ,list_contacts
 WHERE lists.list_id = 15
   AND list_contacts.list_id = 15;
versus
code:
DELETE FROM lists WHERE list_id = 15;
DELETE FROM list_contacts WHERE list_id = 15;

I would go for the single statement because to me it seems if anything simpler. (In most situations like this I would expect a single-table delete statement would suffice, as cascading deletes would take care of the rest.)

# ? Aug 25, 2010 23:20

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Hammerite posted:

I would go for the single statement because to me it seems if anything simpler. (In most situations like this I would expect a single-table delete statement would suffice, as cascading deletes would take care of the rest.)

The first one seems simpler than the second? :confused:

# ? Aug 25, 2010 23:35

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!

Golbez posted:

The first one seems simpler than the second?

Why wouldn't you just add a FOREIGN KEY with an on delete to remove the entires from the other table?

# ? Aug 25, 2010 23:45

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

Golbez posted:

The first one seems simpler than the second?

Now that I look at it again:

I had assumed that "list_contacts" was a child table of "lists", with "list_id" in "list_contacts" being a foreign key to "lists". Assuming that this is the case, I would (if it were my database design) have set up the foreign key specifying ON DELETE CASCADE. Therefore both of the queries you exhibited would be over-complicated because the following would do:

code:

DELETE FROM lists WHERE list_id = 15

If my assumption was correct but for some reason the foreign key isn't actually specified in the CREATE TABLE statement (so the tables, while conceptually related, are independent as far as the RDBMS is concerned), then I would use essentially the first statement but I would rewrite it as follows:

code:

DELETE FROM
    list,
    list_contacts
USING
    lists
    LEFT JOIN list_contacts ON lists.list_id = list_contacts.list_id
WHERE
    list.list_id = 15

The first statement, as you presented it, is in my view badly written because it uses implicit join syntax, which I dislike. That's the only problem I have with it.

If there might be rows from list_contacts in which list_id = 15 but no rows from lists in which the same is true, then the above query would not delete the same sets of rows as the pair of single-table delete queries you presented. (But then I might question the table design, or at least the naming of columns.)

But to give a shorter answer, the first one seems cleaner to me. It may not be as simple as the pair of queries, but nevertheless I would describe it as "simple" because I do not think it unduly complicated.

# ? Aug 25, 2010 23:53

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Sprawl posted:

Why wouldn't you just add a FOREIGN KEY with an on delete to remove the entires from the other table?

I was going to say, "Never used it before, but I'll read up on it and find out if we can".

Then I discovered it's only supported for InnoDB, and we use MyISAM exclusively.

Golbez fucked around with this message at 14:29 on Aug 26, 2010

# ? Aug 26, 2010 14:25

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

Golbez posted:

I was going to say, "Never used it before, but I'll read up on it and find out if we can".

Then I discovered it's only supported for InnoDB, and we use MyISAM exclusively.

Is there a reason? We were using MyISAM and I switched the tables I use to InnoDB so I could use foreign keys. No problems so far except apparently InnoDB saves some kind of log file that can take up a lot of room if you leave it set at the defaults.

# ? Aug 26, 2010 14:42

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Doctor rear end in a top hat posted:

Is there a reason? We were using MyISAM and I switched the tables I use to InnoDB so I could use foreign keys. No problems so far except apparently InnoDB saves some kind of log file that can take up a lot of room if you leave it set at the defaults.

'For the types of data and queries we run, they perform the best'.

# ? Aug 26, 2010 14:54

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!

Golbez posted:

'For the types of data and queries we run, they perform the best'.

Okay your shittin me that's a bunch of lies.

# ? Aug 26, 2010 18:31

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

Golbez posted:

'For the types of data and queries we run, they perform the best'.

If you make heavy use of fulltext indices this might be the case but otherwise whoever said this is probably basing it on data from quite old versions of MySQL.

# ? Aug 26, 2010 19:10

PDP-1: Oct 12, 2004; It's a beautiful day in the neighborhood.

Suppose I have a physical test system where a user places a sample into the rig and runs an experiment that returns a set of a hundred or so (X,Y) data points. I want to store this data in MSSQL, and to my SQL-newbie way of thinking the tables should look something like

ExperimentTable
experimentID
partID
operator
testDate
etc.

DataTable
experimentID
xData
yData

where the experimentID field in DataTable is keyed to the experimentID field in ExperimentTable.

This layout is simple and would work OK on day one, but after a few years of operation the DataTable set is going to get goddamn huge which might result in slow data retrieval, and if I use a 32-bit integer for experimentID and reals for the X,Y data fields a full 33% of the DataTable will be taken up with largely redundant experimentID key info.

Is there a better way to store this kind of information? I guess what I'd really like is some sort of Table-of-Tables where I could store the X,Y datasets in a block and reference them all as one unit. Does that kind of DB structure even exist, and if so what is it called so that I can Google for more info?

Am I overthinking this and falling into the old premature optimization trap?

# ? Aug 27, 2010 15:32

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

We collect much greater amounts of data than that, but we archive it into separate tables once it's a year old. Is that something you can do, or will you need to compare old data to new data regularly? I guess you could make a view that's a union of all the tables.

Are there always going to be the same amount of X, Y data sets? If so, you could make a column for each coordinate and name them appropriately.

# ? Aug 27, 2010 15:39

PDP-1: Oct 12, 2004; It's a beautiful day in the neighborhood.

Doctor rear end in a top hat posted:

We collect much greater amounts of data than that, but we archive it into separate tables once it's a year old. Is that something you can do, or will you need to compare old data to new data regularly? I guess you could make a view that's a union of all the tables.

Thanks for the reply. I thought about doing this and it might end up being the best overall solution even if it does complicate the queries a bit. The users don't see the query however, so it's just a matter of me taking an extra hour or so to write a more general algorithm. If nobody suggests any other solutions it's most likely what I'll end up going with. I just figured it was worth asking to see if there was a better way before I begin.

Doctor rear end in a top hat posted:

Are there always going to be the same amount of X, Y data sets? If so, you could make a column for each coordinate and name them appropriately.

The tests can return a variable number of points and the max number of points that could possible be generated is huge, so I think it's got to stay in column format.

# ? Aug 27, 2010 16:00

butt dickus: Jul 7, 2007; top ten juiced up coaches
and the top ten juiced up players

Specifically we store about 20,000 records daily in one of our tables with an average row size of 550 bytes, which is only ~10 MB per day, so not as much as I thought. I'm guessing your rows should be around 12 or 16 bytes each?

# ? Aug 27, 2010 16:18

PDP-1: Oct 12, 2004; It's a beautiful day in the neighborhood.

Yeah, each record would be about 12 bytes so for an average of 100 points per test it would be a little more than 1kb each run. If they did 100 tests per day for a year it'd generate about 40Mb of data. Run the system for 10 years at that rate and its still less than half a gigabyte.

Looking at it that way I guess I am overthinking things and should just lump everything into one big table. In fact I shouldn't even do the archive-by-year thing, half a gig is nothing to a modern machine.

Thanks for helping put that into perspective.

# ? Aug 27, 2010 16:44

Thel: Apr 28, 2010

gently caress I hate computers. If I have a field in a database with codes and a table linking codes to captions, how hard is it to write a function to take a list of codes and spit out the captions? (MS SQL 2005)

code:

Captions:
Capt_ID:  Capt_Type:  Capt_Code:       Capt_Nice:
9441      stuff         A3               Grumpy
10074     stuff         A1               Doc
10059     stuff         A6               Happy
10079     stuff         A2               Sleepy
9474      stuff         A4               Bashful
9471      stuff         A5               Sneezy
9472      stuff         A11              Dopey


SELECT stuff, magic(stuff)
FROM Client
WHERE (ClientId = 405)


stuff              magic(stuff)
,A1,A2,A3,A6,      Doc, Sleepy, Grumpy, Happy

As you might guess, the Captions table has a bunch of other stuff in there - you find the specific captions you want by looking at Capt_Type. Also, every entry in the stuff column is either NULL, or has commas at the start and end (i.e:

NULL
,A1,
,A3,A4,
,A1,A4,A5,

are all valid entries for that column

(I'm doing this in SQL to make a view for a report I'm writing in Crystal - of the two, I'd much rather deal with SQL.)

# ? Aug 29, 2010 05: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!

That's really easy to do in crystal with a Formula Field.

But its really hard to recommend what to do on the sql side without seeing what is doing the "magic".

# ? Aug 29, 2010 07:37

Thel: Apr 28, 2010

Sprawl posted:

That's really easy to do in crystal with a Formula Field.

But its really hard to recommend what to do on the sql side without seeing what is doing the "magic".

It would be easy to do in Crystal, yes, but I'm trying very hard to avoid doing anything aside from "put the text box here and make it look pretty" in Crystal since we're going to be shifting to SSRS (soon�). Also, wouldn't it be a pain in the rear end to cope with the fact that 'stuff' can have multiple selections?

As far as what 'magic' is, I was thinking a scalar function - one string in, one string out. I'm just wondering how hard it would be to write.

# ? Aug 29, 2010 09:52

Molotov Cock Tale: Jun 30, 2010

Thel posted:

gently caress I hate computers. If I have a field in a database with codes and a table linking codes to captions, how hard is it to write a function to take a list of codes and spit out the captions? (MS SQL 2005)

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

There's several ways there, from the UDF approach to using XML functions to do it for you. On 2005 I'd probably go with the XML method, as UDFs can be a little 'black box' to the query optimiser, meaning it won't be able to correctly guess the output, and might generate a non-optimal query plan.

Edit: Sorry - You mean that the codes are stored as "a1,a2,a3" in a single column's cell? Jesus wept. In that case you can split them into a table variable with a string splitting function - Take a look at http://www.sommarskog.se/arrays-in-sql-2005.html for a lot of ways to do this, pick depending on circumstances and then use the table variable as the base for the XML method as per above

Molotov Cock Tale fucked around with this message at 13:51 on Aug 29, 2010

# ? Aug 29, 2010 13:48

RoadCrewWorker: Nov 19, 2007; camels aren't so great

PDP-1 posted:

Thanks for helping put that into perspective.

Slightly on topic, i have a stupid question.

I've got a simple many-to-many table linking two integer ids A and B with a measly 16 million rows. Mysql tells me the table data is just 137 mb (which is great!) with "Row length � 9".

Now there is also a primary index featuring both columns (A,B) for distinctiveness and an index for each column (A might be redundant since it's a prefix of the primary). These indices weigh in with an additional 617 mb and blow the Row size to 49B - a factor of 5.

So, in theory, is there any way to index this more compactly without loosing too much performance? At this point i kinda doubt it (except maybe for dropping that redundant prefix index) but maybe there's just some nifty trick i'm missing (since int-int many-to-many tables seem like a really really common thing) everybody already knows about.

# ? Aug 29, 2010 16:26

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

RoadCrewWorker posted:

So, in theory, is there any way to index this more compactly without loosing too much performance? At this point i kinda doubt it (except maybe for dropping that redundant prefix index) but maybe there's just some nifty trick i'm missing (since int-int many-to-many tables seem like a really really common thing) everybody already knows about.

The index on A is redundant, yes. Assuming you will have a need to join on B, I don't think you can do better than having two indexes, one on (A, B) and the other on B.

# ? Aug 29, 2010 17:11

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!

Thel posted:

It would be easy to do in Crystal, yes, but I'm trying very hard to avoid doing anything aside from "put the text box here and make it look pretty" in Crystal since we're going to be shifting to SSRS (soon�). Also, wouldn't it be a pain in the rear end to cope with the fact that 'stuff' can have multiple selections?

As far as what 'magic' is, I was thinking a scalar function - one string in, one string out. I'm just wondering how hard it would be to write.

Oh so you haven't done any of the grouping part?

I dont think i quite get the grasp of all your tables so its a little hard to guess at the tables and layout but this should be the jist of it.

code:


SELECT id, SUBSTRING(SELECT '; ' + Capt_Nice + ':' + CAST(Capt_Nice AS varchar(MAX)) 
FROM Client WHERE (ClientId = Results.ClientId ) FOR XML PATH ('')),3,9999) AS Capt_NiceAgg
FROM Client Results
Where (ClientId = 405)
GROUP BY Capt_Type

Alternatively you can do this.

code:

DECLARE @Capt_Nice varchar(8000)
set @Capt_Nice = ''
SELECT @Capt_Nice = @Capt_Nice + Capt_Nice + ','
FROM Client
Where (ClientId = 405)
GROUP BY Capt_Type

If Right(@Capt_Nice , 1) = ','
    Set @Capt_Nice = Left(@Capt_Nice , Len(@Capt_Nice )-1)

SELECT @Capt_Nice As Capt_Nice

Sprawl fucked around with this message at 18:56 on Aug 29, 2010

# ? Aug 29, 2010 18:50

Hoborg: Oct 19, 2006; by T. Mascis

I've got a MySQL 5 server, and I'm wondering if it's possible to restrict a database (not just tables within a database) to a size limit, say 500MB or so.

All the information I've been able to dig up through Google and the MySQL website is about hard limits (such as the MyISAM + Linux/ext4 2GB limit) rather than imposing soft limits like this.

I'm wanting to do this as a precaution against spam filling up in databases. One DB I recently purged had over 800MB of phpBB spam posts (and another 400MB of search indexer rows).

Thanks

# ? Aug 30, 2010 00:28

Pento McGreno: May 4, 2004; Cheese.

Hoborg posted:

I've got a MySQL 5 server, and I'm wondering if it's possible to restrict a database (not just tables within a database) to a size limit, say 500MB or so.

There's no option for this.

Your best workaround is to write a cron to look for stuff to be deleted.

# ? Aug 30, 2010 04:19

Adbot: ADBOT LOVES YOU

# ? May 14, 2024 08:58

Thel: Apr 28, 2010

Sorry if I wasn't clear before - I'm working with a proprietary CRM system, and this is their implementation of multi-selects - and yes it is multiple Capt_Codes in a single element (stored as nvarchar(255)). Clearly tables are a precious commodity but client-side computer time is cheap ... or the developers of this system are incompetent assclowns. :suicide:

I've managed to write a table-valued function to split said string into it's component codes (from which I just need to join to the captions table, then mash everything back into a string). However, there is a complication, namely that the database has to run in SQL Server 2000 compatibility mode, and trying to feed a TVF a field name results in some bollocks about it not being a recognised hint.

In short, gently caress my life and gently caress SageCRM.

# ? Aug 30, 2010 04:46

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

«‹›303 »