|
If you give me sample data (more rows than would be displayed in your desired output) and the desired output, I'll give you a SQL statement that works. Or I'll be really amusing in my attempt.
|
# ? Jan 10, 2008 02:49 |
|
|
# ? May 11, 2024 16:30 |
|
Victor posted:If you give me sample data (more rows than would be displayed in your desired output) and the desired output, I'll give you a SQL statement that works. Or I'll be really amusing in my attempt. code:
|
# ? Jan 10, 2008 16:26 |
|
cletus42o, RDBMS?
|
# ? Jan 10, 2008 17:11 |
|
SQL2005, with a few of my own CLR functions that should be obvious or irrelevant:code:
code:
Victor fucked around with this message at 17:22 on Jan 10, 2008 |
# ? Jan 10, 2008 17:19 |
|
Nice, thanks. I will see if I can get that to work. Took me a second to realize what was going on until I saw the "start reading here." edit - Actually, I'm still not really sure how to use this. Could you show me what I should only need to stick into QA to get the proper results, if you don't mind? SQL 2000, by the way. I also don't actually need a string showing "x+x+x+x+x", I was only doing that to show you what numbers were being summed up. Okay, I think I have it all properly entered.. but it's getting caught on row_number() - what function is this? Is there a 2000 equivalent? cletus42o fucked around with this message at 17:33 on Jan 10, 2008 |
# ? Jan 10, 2008 17:21 |
|
I was lazy and didn't generate create table statements and related annoyances. See http://luke.breuer.com/time/item/SQL_2005_tables_from_strings/201.aspx .
|
# ? Jan 10, 2008 17:25 |
|
So I was able to write this based on some advice I got elsewhere.. (couldn't get your SQL to work, Victor, since row_number() doesn't seem to have any equivalent in 2000 - thanks though!)code:
|
# ? Jan 10, 2008 21:11 |
|
code:
|
# ? Jan 10, 2008 22:35 |
|
The above can be simplified -- I'm too lazy and tired.
|
# ? Jan 10, 2008 22:48 |
|
Victor posted:The above can be simplified -- I'm too lazy and tired.
|
# ? Jan 10, 2008 23:02 |
|
SuddenExpire fucked around with this message at 03:22 on Mar 20, 2015 |
# ? Jan 12, 2008 09:10 |
|
Do you have an index on `DATE`? How about `status`?
|
# ? Jan 12, 2008 11:10 |
|
SuddenExpire fucked around with this message at 03:22 on Mar 20, 2015 |
# ? Jan 12, 2008 14:24 |
|
Is this a single index, or multiple? Indexes can be on multiple columns. In particular, you want an index on (status, DATE).
|
# ? Jan 12, 2008 14:26 |
|
Victor posted:Is this a single index, or multiple? Indexes can be on multiple columns. In particular, you want an index on (status, DATE).
|
# ? Jan 12, 2008 16:53 |
|
We don't know the server's specs.
|
# ? Jan 12, 2008 16:54 |
|
Replace the last two WHERE conditions with a BETWEEN?
|
# ? Jan 12, 2008 17:22 |
|
Ardhanari posted:Replace the last two WHERE conditions with a BETWEEN? That really shouldn't make much of a difference at all.. that query is nothing.. sounds like your machine must just really be a piece of poo poo. Sort of like when I had Dreamhost hosting my MySQL databases...
|
# ? Jan 12, 2008 18:24 |
|
imBen posted:That really shouldn't make much of a difference at all.. that query is nothing.. sounds like your machine must just really be a piece of poo poo. Or it's misconfigured (using my-small.cnf or something similar).
|
# ? Jan 12, 2008 20:28 |
|
deimos posted:Or it's misconfigured (using my-small.cnf or something similar). http://www.forbiddenweb.org/topic/12493/index.html or.. Also to be sure it's actually using the index you could force it on there. http://dev.mysql.com/doc/refman/5.1/en/index-hints.html I've run into trouble with some of my SQL Server queries being "optimized" to run without utilizing an index. I'm sure this problem isn't exclusive to Microsoft.
|
# ? Jan 12, 2008 20:44 |
|
Maybe the planets aren't aligned?
|
# ? Jan 12, 2008 20:58 |
|
Victor posted:Is this a single index, or multiple? Indexes can be on multiple columns. In particular, you want an index on (status, DATE). SuddenExpire fucked around with this message at 00:18 on Oct 7, 2016 |
# ? Jan 12, 2008 23:02 |
|
Victor posted:Maybe the planets aren't aligned?
|
# ? Jan 14, 2008 14:56 |
|
MySQL Fulltext Search Apostrophes Question I'm using a MATCH AGAINST search. I want the search phrase "pete" to match the stored title "Pete's Nougat". Right now only "pete's" matches "Pete's Nougat". Is this possible? increased fucked around with this message at 21:27 on Jan 14, 2008 |
# ? Jan 14, 2008 21:15 |
|
OK, I've got an xquery on SQL 2005 question here. I'm working with an XML structure that we store in a SQL Server 2005 database. The XML structure stored is large, and prohibitive to display all at once via an XSLT transformation, which is the current display method. I'm looking to implement filtering, ordering, and paging on the XML data. I've got a basic XML structure that's something like this(though I've changed certain details): code:
For performance testing, I ran my functions against a rather large XML structure in our database, which contains over 25,000(25,057 to be exact) of those Item tags. This is an especially large structure, but it's not necessarily out of touch with the largest structures in production. My paging function can fetch this large structure and return, say, items 776-800 in about 25 seconds. However, if I were to need those same items returned, but with the data sorted by Weight, the OrderByWeight function takes six and a half minutes to execute the following code: code:
Worse yet, I can apply other order methods that have been asked for, like sorting by "<City>, <State>" on the shipper end. I tried to apply this sort to my large XML file before I went for lunch, thinking I'd check the execution time when I returned. 30 minutes later, it was still executing, at which point I cancelled it. That code was very similar to the above, just like this instead: code:
|
# ? Jan 14, 2008 22:03 |
|
Filash posted:MySQL Fulltext Search Apostrophes Question Also, the problem may not be that it's not finding Pete's Nougat, but rather that "Pete" might be too short a search phrase for the fulltext. Change the "ft_min_word_len" setting; I believe the default is 4. Try that first before you try changing to a boolean search.
|
# ? Jan 14, 2008 23:48 |
|
Golbez posted:I'm finding fulltext to be less than useful by itself... I think fulltext has a lot of problems too. Fortunately this solution is only going to be in place for a few months. ft_min_word_len is 3. I still wonder if there's a way to make it so that "pete" finds "Pete's" by changing the MySQL config (and not by massaging the string somehow).
|
# ? Jan 15, 2008 00:06 |
|
I have a PHP script that, at the very top of the script, makes a MySQL query that grabs a bit of data from the database, and makes that data global. So it looks like this:php:<? $query = "some query"; global $data; $data = mysql_query($query); ?> When the functions use the data, they do it with mysql_fetch_array() and a while() loop. At the end of each of those loops, I have "mysql_data_seek($data, 0);", but it still thinks each one, except for the first, is empty. What am I missing?
|
# ? Jan 15, 2008 18:37 |
|
It seems like you have it set up right, but maybe there's a flaw in your code you are overlooking. What do your while loops and calls to mysql_data_seek() look like. Also, did you mean each loop, except the first is trying to loop thru empty result sets, or that the result sets are empty except for the first row.
|
# ? Jan 15, 2008 22:17 |
|
I've got a table with data like this:code:
So that part would be something like: code:
code:
rocketrobot fucked around with this message at 23:23 on Jan 15, 2008 |
# ? Jan 15, 2008 23:13 |
|
Ok, so I got it. Had to create a unique ID field for each row, but this is how it went down:code:
|
# ? Jan 16, 2008 00:19 |
|
Use a cursor: This is getting pretty sophisticated, you can probably use some SQL hack to do it, but in the long run doing it "the right way" will be best.
|
# ? Jan 16, 2008 00:20 |
|
Xae posted:Use a cursor: This is getting pretty sophisticated, you can probably use some SQL hack to do it, but in the long run doing it "the right way" will be best. Our DBAs don't let us use cursors because they suck. I suppose I could have forced the issue and made them let me use one, but this works.
|
# ? Jan 16, 2008 00:41 |
|
Get your DBAs fired. They are worthless and are providing nothing of use to your organization. They should be fired and replaced immediately. I'm not joking. I'm too lazy to check, but I would wager that query has an implicit cursor in it. If they won't fire them switch jobs, you're crippling your development as a programmer, unless you really like it there or something... Xae fucked around with this message at 02:04 on Jan 16, 2008 |
# ? Jan 16, 2008 02:02 |
|
Filash posted:I think fulltext has a lot of problems too. Fortunately this solution is only going to be in place for a few months. Elaborate please, are they getting rid of/drastically fixing fulltext in a few months?
|
# ? Jan 16, 2008 02:58 |
|
Xae posted:Get your DBAs fired. They are worthless and are providing nothing of use to your organization. They should be fired and replaced immediately. Haha, no, I'm getting out some time this year. The whole place is hosed.
|
# ? Jan 16, 2008 04:16 |
|
I have a general SQL table design question. Let's say I have a table that stores "application events". The table needs to refer to the source of the event, and these sources may be of various different types, from various unrelated sub-systems of the application. E.g. - Forum thread X was updated - File Y was uploaded - User Z was added I'm trying to work out the best way of designing a table to store the reference to the source, and using a FK constraint as well (so that deleting the source of the event would also delete the event). Option 1) 3rd-normal form ahoy! ----------------------------- code:
Pros: +More space efficient (to an extent) +More correct database normalization Cons: -Suckier to work with (requires JOINs, more complex collation code) -Small performance hit from the FK constraints on event.event_id -A new table is required to add a new type. Option 2) Use a single table: ----------------------------- code:
Pros: +Simple to understand +Means only searching one table, less schema management +A new column is all that's required to add a new type. Cons: -Should implement a row "CHECK" to ensure that exactly 1 of the event-specific fields was chosen. -A little more code required to identify which type of event this is. Option 3) Store a "type" and ID ----------------------------- code:
Pros: +Succinct and simple Cons: -Not possible to use FK constraints on "id". -Alarmingly like the "Entity-Attribute-Value" anti-pattern. In the past I've implemented Option 1, like a good little database normalizer. However the clunkiness of doing so has led me to want to implement Option 2. The rest of my team (not very DB-design-savvy) is advocating Option 3, which to me is right out because it would take god himself to pry FK constraints from my dead hands. Has anyone had any experience on what is the best approach?
|
# ? Jan 16, 2008 05:28 |
|
If you care about your data option 1 is best. If not option 2 works decent enough.
|
# ? Jan 16, 2008 05:36 |
|
minato posted:Option 2) Use a single table: Option 2 is basically how my company does logging enterprise wide. Not that everything we do is right... lots of times it's not.. but it seems to work pretty good overall.
|
# ? Jan 16, 2008 05:52 |
|
|
# ? May 11, 2024 16:30 |
|
As it happens, I implemented an event log system almost exactly like that. We've got over 300 different types of events, and maybe six or eight different kinds of event "targets". One basic caveat: I would seriously recommend that you reconsider wanting to builid in those FK constraints. They'll really load things down. In our system, we've got literally millions and millions of events - every day's batch processing causes many hundreds of thousands of new ones to be logged. Those constraints will make deletes take forever if you're not careful. Also, if your event log is anything like an audit trail, why would you want entries in it to go away? Things that happened don't "un-happen" when the target is removed from the system. Our events track the target info, the "actor" responsible (if any), the event type, the timestamp, and disposition information (our events can generate audit email, and we have to keep track of whether an event has been thusly processed). There's a head table to keep track of common attributes (like you said), and then auxilliary tables by event type for extra attributes. Our events are defined in an XML file, and some code generates Java classes and SQL code for maintaining stuff. The generation happens automatically as part of the build. We used to keep FK constraints on the aux tables back to the head table, but that was causing us real problems. We've never kept FK constraints back to target objects.
|
# ? Jan 16, 2008 13:30 |