|
Beelzebozo posted:I normally recommend against separately storing an end_date in cases where a set of date ranges should form a full cover, such as an audit of a status change. Not because of any storage redundancy or performance concern, but that just means that humans have to maintain the full cover property and opens up the possibility of somebody creating a situation where there are overlapping ranges or parts of the time series not covered by any range, which creates an unknown semantics scenario that software or reports might not expect. That was my thought as well; it's highly unlikely but possible that the end date of one record and the start date of the next record become out of sync, or what not. This is being used to store iterations of account information, so there will never be any overlap and never any gaps. Make a change, a new record gets inserted, maintaining a solid audit trail.
|
# ? Feb 7, 2013 18:57 |
|
|
# ? May 30, 2024 13:52 |
|
Golbez posted:there will never be any overlap and never any gaps You have no need to store an end_date. The end_date for each row can be presumed to be the day prior to the begin_date of the next row, or "current" if there is not a next row by date for that key.
|
# ? Feb 7, 2013 19:11 |
fletcher posted:
Just to follow up on this, increasing myisam_max_sort_file_size seems to have fixed my issue. Hooray!
|
|
# ? Feb 7, 2013 20:03 |
|
Beelzebozo posted:You have no need to store an end_date. The end_date for each row can be presumed to be the day prior to the begin_date of the next row, or "current" if there is not a next row by date for that key. Is there an easy way, then, to get the range for a particular row in one query?
|
# ? Feb 7, 2013 20:38 |
|
Golbez posted:Is there an easy way, then, to get the range for a particular row in one query? Check out window functions, you can use them to easily roll up the next record.
|
# ? Feb 7, 2013 20:45 |
|
Pardot posted:Check out window functions, you can use them to easily roll up the next record. What are my options within MySQL?
|
# ? Feb 7, 2013 20:54 |
|
Yeah, sorry, I'm a postgres DBA, and I would use window functions. They've been in the standard since 2003. I have no idea how to do anything in MySQL that isn't standard SQL, I'm afraid.
|
# ? Feb 7, 2013 20:59 |
|
Golbez posted:What are my options within MySQL? Do yourself a favor and migrate I honestly have no idea. If people would mention that they're unfortunately not on postgres I can stop answering peoples questions with features they don't have access to
|
# ? Feb 7, 2013 20:59 |
|
Golbez posted:Is there an easy way, then, to get the range for a particular row in one query? This logically does what you want, I don't know if a better way exists. code:
|
# ? Feb 7, 2013 22:23 |
|
So what you're saying is, since I'm on MySQL, I should include an end date.
|
# ? Feb 7, 2013 22:32 |
|
Golbez posted:So what you're saying is, since I'm on MySQL, I should include an end date. You could make it a view I guess. Whether you should make it an end date maybe depends how often you're going to require end dates. It just occurred to me, if you do add an end date column you might be able to use foreign key constraints to make sure that the end date and start date data remain consistent. This might require a null start date for the first row. I haven't really thought this through.
|
# ? Feb 7, 2013 22:42 |
|
Golbez posted:So what you're saying is, since I'm on MySQL, I should include an end date. Hammerite posted:You could make it a view I guess. Whether you should make it an end date maybe depends how often you're going to require end dates. Yeah, if you are worried about dates overlapping, enforce it with a constraint. But how often are you going to need to see the duration of a specific row? I know I brought this up initially, but my line of thinking was less around an audit trail and more around a dimension in a data warehouse. For an audit trail I would think just storing the time the change occurred would be all you need. If you really want to report on it, use a view and Hammmerite's query looks like it would do the trick. hewerman fucked around with this message at 02:29 on Feb 8, 2013 |
# ? Feb 8, 2013 02:20 |
|
I've been messing around with using various sql databases to store a massive amount of time series data (100M-1B rows), essentially denormalized data where the first column is a timestamp, and that column is *sorted by time*. I've noticed that some column stores optimize for this case already (It looks like they're keeping track of the range of values in each slice, so they can throw away massive amounts of data when I query over specific time periods) -- but for those that don't, is there any specific type of index that will help for queries of the form (SELECT * FROM timeSeriesTable ... WHERE timestamp < X AND timestamp > Y)? Most of the solutions are MySQL engines or forked from Postgres a long time ago.
|
# ? Feb 12, 2013 07:23 |
|
Chuu posted:I've been messing around with using various sql databases to store a massive amount of time series data (100M-1B rows), essentially denormalized data where the first column is a timestamp, and that column is *sorted by time*. If it is available to you, clustered index on that column, or a set of columns including the timestamp comprising a unique key, will give you the biggest bang for your buck.
|
# ? Feb 12, 2013 16:11 |
|
SQL code:
Am I wrong in expecting this? I'm worried because this table is essentially a log that will grow forever, so eventually this query ("has this driver punched in today?") will get slow if it needs to scan the table every time.
|
# ? Feb 14, 2013 20:00 |
|
What is the definition of the clustered index/primary key? Also, can SQL Express have multi-column indexes? If so, try creating one index that uses both DriverId and PunchInTime. Nth Doctor posted:[Definitions of clustered index/primary key] Jethro fucked around with this message at 16:01 on Feb 15, 2013 |
# ? Feb 14, 2013 23:45 |
|
Using Postgres, I have a multi-table join, and I basically need to join one of the tables on a condition of non-existence, but that's not really how a join works, so I'm having trouble figuring out what to do. I'll try to give a minimal example: SQL code:
code:
SQL code:
|
# ? Feb 15, 2013 00:16 |
|
Maybe try wrapping that query in a CTE?code:
|
# ? Feb 15, 2013 00:40 |
|
Jethro posted:What is the definition of the clustered index/primary key? A clustered index defines the order in which the table is laid out. It's like the page number in a book. Nonclustered indices are like looking up an item in the index in the back of a book, giving a pointer to the page the information you are after. A primary key is a column or set of columns that comprise a unique fingerprint for each row in the table.
|
# ? Feb 15, 2013 03:07 |
|
Kilson posted:Using Postgres, I have a multi-table join, and I basically need to join one of the tables on a condition of non-existence, but that's not really how a join works, so I'm having trouble figuring out what to do. CTEs won't help you here. What you're trying to do is called an anti-join, and in postgres these are expressed as a left outer join where the right side is NULL. In your example, you probably want something like: SQL code:
|
# ? Feb 15, 2013 14:08 |
|
Beelzebozo posted:CTEs won't help you here. What you're trying to do is called an anti-join, and in postgres these are expressed as a left outer join where the right side is NULL. This gave me exactly the same results as the CTE, and they're both unfortunately wrong. code:
code:
A minor change fixed it though. In the second select, I just have to join phone_number on the conditions that the place_id matches AND the user_id is null. Thanks guys!
|
# ? Feb 15, 2013 16:26 |
|
Kilson posted:Because d2 isn't mapped specifically to a phone number, and the only phone number in the same location as d2 has a user, so it's not allowed to be used except when tied to the mapping (of d3, in this case). Oh, yeah, if you'd typed that just like that it would've helped. I completely misunderstood your question. Glad you got it worked out though.
|
# ? Feb 15, 2013 17:20 |
|
I set up a mysql express database and am trying to get it to work with wordpress. I keep getting an error for establishing a database connection and I feel like it is a permission issue with IIS. Anyone have any ideas? I can log into the sql database fine with the user name and password.
|
# ? Feb 15, 2013 21:49 |
|
Hi, i hope i'm at the right place for my question....it's stupid, so it should fit. How can i make this stuff shorter? I just want to get the userid and username for all users. I also use this, even if i only want one user, then with "SELECT * FROM users WHERE userid='$userid'" php:<? $sqlquery = "SELECT * FROM users ORDER BY username"; $reading = mysql_query($sqlquery); while($row = mysql_fetch_object($reading)) { $userid=$row->userid; $username=$row->username; echo somevariables; } ?>
|
# ? Feb 15, 2013 22:08 |
|
RabbitWizard posted:How can i make this stuff shorter? Switch to a language that has less boilerplating than PHP. Not trolling. That's a sincere answer. What you have there is about as terse as PHP can go on that operation.
|
# ? Feb 15, 2013 22:09 |
|
Beelzebozo posted:Switch to a language that has less boilerplating than PHP. Not trolling. That's a sincere answer. What you have there is about as terse as PHP can go on that operation. To be fair, he could use an ORM too. PHP sucks pretty hard but it's used drat near everywhere and surely there are some smart people dealing with it. I'm not familiar with the frameworks available for PHP, though, but I bet there's something like Hibernate or SQLAlchemy available. Maybe search for those. As for a specific thing that may help improve code quality, try selecting only the UserId and UserName like "select username, userid from users order by username". I don't know how wide your Users table is, but it's more optimal to select only what you need in a query instead of *. Just less data to transfer over pipes or a network connection or what have you. If your method is re-used a lot, you might not be able to avoid it, of course.
|
# ? Feb 15, 2013 22:39 |
|
Thanks for your answer Beelzebozo, the stuff i understood (1/4 of it) was kinda funny, but i really don't want to learn another language because i know what i want to do works with php. It won't be a page open to the public, so i don't have to deal with stupid inputs or sql-injections. Maybe there is a chance no one will ever see my code, so i can program as lovely as i want. Even if someone saw it, i would have made my money by then. It's just for myself, i don't want to have 3 lines if it could be one because.......well, because. Anyway, if i ever should sell my PoS, you get from me for answering. Could take a while, but i noted your username (Yeah, chances are (very) slim, but i think i could sell it) ninjaedit: for glompix as well, if i ever sell it
|
# ? Feb 15, 2013 22:43 |
|
RabbitWizard posted:Thanks for your answer Beelzebozo, the stuff i understood (1/4 of it) was kinda funny, but i really don't want to learn another language because i know what i want to do works with php. I wonder who's the sucker lining up to buy your crap.
|
# ? Feb 15, 2013 23:04 |
|
RabbitWizard posted:i can program as lovely as i want. Even if someone saw it, i would have made my money by then. gently caress, just make it your resume because.......well, because.
|
# ? Feb 15, 2013 23:18 |
|
Well, thats easy....at thist company, i wanted to make a small database for all the stuff we need.....instead i got fired (because i was sick and didn't want to show up sick in a company that sells food-items). So the company asked a "real" company to do their database. 10k for analysing their needs(which they paid, and then didn't take the offer), 100k for the the finished product. Instead of me taking a 2k course to learn the software and 5k for 1 licence. So i thought about writing my own software for them. Even if it takes 4 weeks, if i can sell it to those motherfuckers, it would be worth it. I know what they need, and i can give it to them. Of course a relative of mine would sell it to those assholes, not me. So it could work. Yes, i'm still bad with php, but you won't believe the poo poo they bought over the years i was there. What i learned is that if it doesnt cost much, it can't be worth anything. And i think i have a good chance of loving them over. (Germany!!!!!!!!)
|
# ? Feb 15, 2013 23:19 |
|
RabbitWizard posted:Well, thats easy....at thist company, i wanted to make a small database for all the stuff we need.....instead i got fired (because i was sick and didn't want to show up sick in a company that sells food-items). So the company asked a "real" company to do their database. 10k for analysing their needs(which they paid, and then didn't take the offer), 100k for the the finished product. Instead of me taking a 2k course to learn the software and 5k for 1 licence. Let us know how this goes!
|
# ? Feb 15, 2013 23:48 |
|
I sure will! SHSC not believing in me gives me affirmation i never needed, but like i said, theres a small chance it will work At least i know i posted in the right thread. Thanks glompix for your answer. I will be silent now until i make my big bucks.
|
# ? Feb 15, 2013 23:56 |
|
Dumb question, but is there an easier way to search based on a single column with multiple values? ie. "SELECT * FROM Table WHERE PK = 1,2,3" instead of: "SELECT * FROM Table WHERE PK = 1 OR PK = 2 OR PK = 3" The latter is just so much uglier...
|
# ? Feb 21, 2013 15:58 |
You're looking for the IN operator:code:
|
|
# ? Feb 21, 2013 16:02 |
|
Oh, hm, I guess I knew that. Just haven't used it in a long time. Thanks
|
# ? Feb 21, 2013 16:08 |
|
I am not a DBA, so I was wondering if anyone out there could clear up any of my thoughts regarding a current issue at work. We are using SQL Server 2008 R2 (I think) Enterprise edition. To simplify the problem, we have these two databases with the same schema, A and B, that are both on the same server, and we want to keep their data in sync with each other. Actually, we just want to make sure that whatever data is in A also gets put into B in a sort-of timely fashion. If inserts happen in B, A doesn't need to know about it. Basically, B is the one that should have the full set of data. B is a production database that people will potentially be querying and inserting into 24/7. A only exists because there is a secondary way to insert data into our system, and in a round-about way ends up in A (migrated via mapping scripts from a bizarre database on our unix system). This is all still in the design phase for at least a little longer and I've only now become a part of examining it, and their current plan seems odd to me: they've designed B to have 2 sets of each table from A, and a view for each set of tables. Someone wrote a merge script to push data from A to B into one set of tables, the other set being left open so B can still be queried via the view, which will be hooked up to the non-merging tables (the assumption being the merge script locks the tables on B). The script alternates which table set it merges into so that both sets get the new data from A eventually. I think the primary keys are kept distinct by a separate column on each table identifying its source, which becomes a composite with the traditional ID column. This all seems like a weird work-around to me, and from my very preliminary googling it seems like we can maybe use Snapshot Replication from A to B every so often to accomplish this same task (I think without the convoluted scripts) and it would also not lock the tables in B, who would be the subscriber. Does anyone more knowledgable have any advice to offer in my situation? Are either of these ideas viable? Is something terribly dumb? Thank you for reading about my problems.
|
# ? Feb 22, 2013 17:28 |
|
Opulent Ceremony posted:I am not a DBA, so I was wondering if anyone out there could clear up any of my thoughts regarding a current issue at work. We are using SQL Server 2008 R2 (I think) Enterprise edition. I don't claim to understand the convoluted data dance going on here with the multiple tables/views at all, but an issue you'll run into with replication technologies are primary key collisions. I believe all replication types (certainly transactional and merge, probably snapshot) try to make the primary keys match up to the source. If you're not using natural keys everywhere, you'll hit collisions. It's not impossible to deal with (segment your key ranges somehow is the easiest way) but it's something to think about. A dead-loving-simple extract/transform/load solution would probably be the only way to go for this, and SQL Server Integration Services might be able to help you with it. I don't know much about it. Also, if you're worried about locking... are you using snapshot isolation?
|
# ? Feb 22, 2013 18:13 |
|
Thanks for the thoughts. Regarding Snapshot Isolation, I don't believe it is currently being used and was unaware of this feature, thanks!
|
# ? Feb 22, 2013 19:50 |
Is this thread open to NoSQL questions as well? If so, I have a collection of usernames which own a resource. I could put them as an array inside of a document in the collection, or, alternatively, I could make each of the usernames in the array a single document. Are there any general design policies one way or another? The query method would likely be single 'is foo in this set' type operations.
|
|
# ? Feb 22, 2013 21:39 |
|
|
# ? May 30, 2024 13:52 |
|
I'm having trouble writing a MySQL query for a query for displaying a college courses prerequisite courses. Each course only has at most a single prerequisite, with some courses having none. The query requires two tables, a course and prerequisite table. They have the following schema: `course` (`course_id`, `title`, `dept_name`, `credits`) `prereq` (`course_id`, `prereq_id`) I wrote a crude and obvious query using natural join, but I've been stumped since... code:
1.) Some of the courses don't have prerequisites. For these results in the query, the `prereq` column should say "None" or "N/A". In it's current form, this query completely omits courses without prerequistes. 2.) `course_id` and `prereq_id` are both varchars in the form of XXX-XXX. The query result needs to include the course `title` and prereq `title`, also varchars (note that `title` is not on prereq's schema). I have been experimenting with both temporary tables and nested queries, but I can't get the prerequisite column to display anything but the `prereq_id` instead of an equivalent `title`.
|
# ? Feb 25, 2013 01:00 |