Bonus posted:You can see how efficient your query is. How much records needed to be traversed to get the results of the query. For instance, you almost never want to do full table scans. It's good to check your queries with explain to see if full table scans are happening at certain queries so you can introduce indices or reform the queries. So how do I read and interpret the results of the EXPLAIN to do this?
|
|
# ? Dec 4, 2007 20:17 |
|
|
# ? May 13, 2024 11:33 |
|
Wow, I can see how reading EXPLAIN results would be a bit painful. Compare that to MS SQL Server query plans:
|
# ? Dec 4, 2007 20:23 |
|
Check out Aqua Fold "DataStudio" for a free, flawed, but overall useful tool to provide graphical "EXPLAIN" rendering for a variety of databases.
|
# ? Dec 4, 2007 20:34 |
|
I've run into some spooky MySQL stuff recently. Specifically, expressions being evaluated for each row of a resultset in a manner I'm really not comfortable with. I'm looking for a running total of some values. This, at least in my past experience, is difficult with only SQL (at least, if you avoid cursors). I've now found that I can do this in MySQL: code:
If I were to try the same in MS SQL: code:
Is there a way to get a running total from MS SQL in the same manner as from MySQL? Or, as I suspect, is getting a running total from MySQL a bad idea to begin with?
|
# ? Dec 5, 2007 15:41 |
|
How does the MySQL example actually do what you think it is doing? You are grouping by UserID, but only getting one row back -- do you only have one user? If you are convinced that MySQL really is doing the proper thing, do post the results, because I would be very surprised. The TSQL version can be fixed by only assigning to variables, and not retrieving data. However, be warned that, at least if you throw in an ORDER BY clause, SQL Server is not guaranteed to populate your variable properly. If you'd like, I can yank a script that generates pretty CREATE TABLE code from INFORMATION_SCHEMA, which assigns to a @sql variable in the select statement in the style above, and breaks (only returns one row) if I add an ORDER BY ORDINAL_POSITION, in SQL 2005 (SQL 2000 works fine with that ORDER BY). Edit: if you're using SQL 2005, do this properly by writing an SQL CLR aggregate. I have two such aggregates: dbo.JoinWithPipe and dbo.JoinWithCommaSpace.
|
# ? Dec 5, 2007 15:53 |
|
Melraidin posted:(...) Calculating Running Totals, Subtotals and Grand Total in MSSQL Without a Cursor
|
# ? Dec 5, 2007 16:10 |
|
Victor posted:How does the MySQL example actually do what you think it is doing? You are grouping by UserID, but only getting one row back -- do you only have one user? If you are convinced that MySQL really is doing the proper thing, do post the results, because I would be very surprised. You're right that my example sucked. I tried to write something that would be a bit easier to understand than the actual code, but I sure screwed that one up... A better example, I hope (better since it's not broken): code:
In MySQL the equivalent of this query on my DB gives: code:
Could you elaborate on your edit? I'm not certain what portion of my post this is in reference to. Alex007 posted:Calculating Running Totals, Subtotals and Grand Total in MSSQL Without a Cursor Thanks for the link, and this does do what I need. This article shows examples of solving this in the manner I would expect, but hadn't thought of. The major difference between this method (a sub-query for each row) and the MySQL method, at least from my understanding, is the MySQL method will only do the single query on the table, while the article's method will be doing Number of Rows + 1 queries on the table.
|
# ? Dec 5, 2007 16:29 |
|
Melraidin posted:Thanks for the link, and this does do what I need. This article shows examples of solving this in the manner I would expect, but hadn't thought of. The major difference between this method (a sub-query for each row) and the MySQL method, at least from my understanding, is the MySQL method will only do the single query on the table, while the article's method will be doing Number of Rows + 1 queries on the table. If you want a GREAT book with a lot of complex problems solved, read Joe Celko's "SQL for Smarties - Advanced SQL Programming", it's an AWESOME book with lots of great solutions.
|
# ? Dec 5, 2007 16:39 |
|
What version of SQL Server are you using? I realize I misread your first post after you posted sample results, so I'm pretty sure you can't do what you want with variable assignment, but I think you can another way. SQL2005 offers a lot of sweet functionality that could potentially make this a lot easier.
|
# ? Dec 5, 2007 17:58 |
|
Victor posted:What version of SQL Server are you using? I realize I misread your first post after you posted sample results, so I'm pretty sure you can't do what you want with variable assignment, but I think you can another way. SQL2005 offers a lot of sweet functionality that could potentially make this a lot easier. MS SQL Server 2000 I believe. Even so, could you point me to these functions in SQL Server 2005, just for interest's sake?
|
# ? Dec 5, 2007 19:23 |
|
This guy says cursors are the best method for doing running totals. Here are a few different ways to do running totals. I don't have time to explain them now; I can do that later if necessary. Only one of them requires SQL Server 2005; it uses recursive CTEs. code:
|
# ? Dec 5, 2007 20:53 |
|
Melraidin posted:Is there a way to get a running total from MS SQL in the same manner as from MySQL? Or, as I suspect, is getting a running total from MySQL a bad idea to begin with? Pull the data out of the database as-is, and model the data in your application. Keeping computation out of your database makes it perform better, and promotes a looser coupling between your application and any one database platform. Just my opinion!
|
# ? Dec 5, 2007 21:28 |
|
^^^What that man said. Most reporting layers easily support running totals with ease.
|
# ? Dec 5, 2007 21:30 |
|
fletcher posted:So how do I read and interpret the results of the EXPLAIN to do this? I found a great tutorial to EXPLAIN and indices at http://hackmysql.com/ Specifically, the urls http://hackmysql.com/case1 and up, just change the number.
|
# ? Dec 5, 2007 22:01 |
|
Related to my post in the Excel thread. This is probably really, really simple for anyone who actually knows SQL well. Me? It's just that thing that ActiveRecord hides from me. And my only experience is with MySQL, not MSSQL. But man, you stick a JOIN in there and my eyes just glaze over. In this spreadsheet we have two queries. One, amusingly, queries the other. I'm trying to figure out why. Is there any reason this: code:
code:
I've been over this in my head lots and lots of times and I can't see how a query with more criteria would somehow return more data. The only thing I can think of is that FULL OUTER JOIN in there, but that doesn't make a lot of sense (actually, the existence of the entire join doesn't make sense) because one of the criteria is for that table not to be null. The way the Excel sheet works is that it takes each query and places them into a table. Then there's a field in the first query's table that checks the second query's table for the existence of the hash, and if it's not there, it assumes there was never a response and says so. But it doesn't make a lot of sense to me - they're both querying the same table, they're both using the same restraints - the one that returns "more" data actually has more restraints... I have the feeling that if I could condense this into a single query then I could merge this one with the other query in the spreadsheet and be done with it. Also, I don't know a lot about SQL, but is this an incredibly messy query or is it just my inexperience with the language? Dessert Rose fucked around with this message at 02:21 on Dec 6, 2007 |
# ? Dec 6, 2007 02:14 |
|
That SQL is revolting, especially the table breaking part. The SQL formatter in the OP sucks, IMHO. You have two tables in the first query that don't show up in the second query. code:
|
# ? Dec 6, 2007 02:47 |
|
Victor posted:That SQL is revolting, especially the table breaking part. The SQL formatter in the OP sucks, IMHO. Glad to know I'm not the only one who thinks it looks like poo poo. Wouldn't an inner join not actually add any records to the result, though? My understanding of INNER JOIN is that it only pulls extra records in if there's something on both sides.
|
# ? Dec 6, 2007 03:04 |
|
Let's say I run a query on the table Thread. Then I add an inner join to Post. Will not the number of results balloon?
|
# ? Dec 6, 2007 03:10 |
|
Victor posted:Let's say I run a query on the table Thread. Then I add an inner join to Post. Will not the number of results balloon? Okay, that makes sense for a has-many association. To rephrase: Look at the two tables in question. A given install will only have one arch and only one sku; it's impossible to install the x64 and x86 versions simultaneously, for example. So, given that I'm only doing inner joins on tables that have one-to-one relationships with the initial table, is the same true? This is also a little off what my actual question is. Is there a way to construct a query such that I only get the results returned by the first query, that aren't returned by the second? I look at the queries and what I come up with is already in there (the OperationResult.NAME IS NULL bit) but all I'm really doing is stumbling in the dark. And of course I can't find the guy who wrote this to begin with to beat him with a stick. Which is understandable; I know I wouldn't want to maintain this crap.
|
# ? Dec 6, 2007 03:20 |
|
Just the thread I need! I need some help. I have a table called relations. It stores an OBJECT, its PARENT, and the objects VERSION. So, for example, code:
4567 1234 98989 4522 4356 91102 My query is: code:
4567 1234 98989 4567 4356 98989 4522 4356 91102 Just throwing a distinct command after the select distinct obviously won't do it. I've tried constructing a nested join but can't get it right (I keep putting things in the wrong order, like - select distinct version from relations in (select...)). Can anyone help? I'm totally lost. mister_gosh fucked around with this message at 03:48 on Dec 6, 2007 |
# ? Dec 6, 2007 03:45 |
|
mister_gosh posted:Can anyone help? I'm totally lost.
|
# ? Dec 6, 2007 04:43 |
|
The cheap way around this is to just use max() or min() to get the one you want. Like:code:
|
# ? Dec 6, 2007 04:45 |
|
Satan's Scallion posted:The cheap way around this is to just use max() or min() to get the one you want. Like: Works perfectly!
|
# ? Dec 6, 2007 05:16 |
|
Ryouga Inverse posted:Look at the two tables in question. A given install will only have one arch and only one sku; it's impossible to install the x64 and x86 versions simultaneously, for example. quote:Is there a way to construct a query such that I only get the results returned by the first query, that aren't returned by the second? I look at the queries and what I come up with is already in there (the OperationResult.NAME IS NULL bit) but all I'm really doing is stumbling in the dark. code:
code:
|
# ? Dec 6, 2007 05:19 |
|
Victor posted:Have you verified that they are one-to-one? The only way I can see this not being the culprit is if I don't fully understand the semantics of full outer join when used like you have in your example. You can figure this out (and report back to me): select sessionid from all the tables in the first query and tell me if any are ever null. I think we might be... I don't know what the database is running but I have a feeling it would be 2005 So just so I "get" your pattern, what I would do is essentially create two derived tables, one from each query, and then do that left join on it? I d derived tables and that aspect of it makes sense to me. What does "t2.a = t1.a or t2.a is null and t1.a is null" do in the context of a join, exactly? Or would I just replace that with some entirely different code, like "t1.v_spoperationtype.sessionid = t2.v_spoperationtype.sessionid"?
|
# ? Dec 6, 2007 07:30 |
|
Ok, if you have SQL2005.. just comment out the columns in each query that aren't found in another, make them in the same order in each query, then stick an except in between the two queries and try to run it. With derived tables, you'd literally replace "massive query here" with your massive query. However, using except is much cleaner and leaves much less room for error, so try that!
|
# ? Dec 6, 2007 12:03 |
|
$5 paypal to whoever can fix this: Now I have a situation where I'm retrieving an OBJECT. Once I have the number, I want to get the OBJECT parent but only if it is of TYPE 201. So for example, I'm working with OBJECT 4567. Since there are multiple PARENT entries, I only want to get the one that is of TYPE 201 but that info is in a separate table. TABLE RELATIONS: code:
code:
mister_gosh fucked around with this message at 05:10 on Dec 7, 2007 |
# ? Dec 7, 2007 05:06 |
|
You mean likecode:
Sounds like you just need a primer on basic SQL joins, there has to be a million of them out there. Edit: please don't paypal me anything
|
# ? Dec 7, 2007 05:16 |
|
Satan's Scallion posted:You mean like In this instance, I know as I construct the query the OBJECT value, in this case 4567, so I want to include that in the conditions to ensure I only get one or so results. I also failed to mention area needs to be included in this test. This appears to work: code:
|
# ? Dec 7, 2007 05:34 |
|
Sort of on/off topic, BUT;- If anyones ever wondered about how sql type databases actually work at the core, have a read of this;- First read this;- http://www.sqlite.org/arch.html Then read this;- http://www.sqlite.org/vdbe.html Its on how the SQL engine in SQLLite's opcode system works. Its fascinating stuff, and really is quite enlightening the process of going from really high level SQL query, to snappy-as-gently caress low level data retrieval. Its amazing stuff (Probably more so , considering what a wonder that little database really is.) Honestly, after having a dig around the *excelent* developer documentation for SQLLite, I now know a hell of a lot more about how these things work than I used to. Required reading.
|
# ? Dec 7, 2007 08:55 |
|
I have a table that's basically a log of searches that has a userid, the search, and the timestamp as the columns. I'm trying to run a query that gets a users last few unique searches. Let's say I have a table like:code:
code:
What I actually get is you and bar. I suspect that the last foo is cut out of the results because the DISTINCT clause matches it with the first foo and eliminates it before the ORDER BY is ever applied. Is there some way to get around this?
|
# ? Dec 8, 2007 05:15 |
|
This seems...wrong somehow, but creating an index on `timestamp` will make your query return the correct result. At least on MySQL 5. edit: I mean I get why it works, it just seems to me that an index shouldn't affect the result of a query. Land Mime fucked around with this message at 06:01 on Dec 8, 2007 |
# ? Dec 8, 2007 05:58 |
|
Since foo is not distinct, it is not part of the result. try a GROUP BY on search and select MAX(timestamp)
|
# ? Dec 8, 2007 05:59 |
|
Land Mime posted:This seems...wrong somehow, but creating an index on `timestamp` will make your query return the correct result. At least on MySQL 5. The reason it seems wrong is because MySQL is silently letting you get away with doing something you shouldn't be able to, and that is wrong. When you refer to a non-grouped column anywhere in your query, what are you really asking? For example... code:
Other databases, like Oracle, will actually error if you try to do this; you are required to have all columns in either your GROUP BY or wrapped in aggregate functions. MySQL just gives you "whichever" and I don't know if it's well-defined. It seems like adding an index has altered the result for you and should be a good indication to not trust it long term.
|
# ? Dec 8, 2007 06:09 |
|
I disagree, I don't think it's wrong, although in my attempt to figure out a good reason to debate your point, I couldn't think of anything that couldn't really just be solved with a MIN, MAX, or GROUP_CONCAT, or a well defined query. So it may be wrong, but it definitely make things easier at to just have MySQL overlook and just give you whichever column it feels like.
|
# ? Dec 8, 2007 06:17 |
|
noonches, you have to be kidding me. You're advocating an RDBMS which returns different results based on configuration like indexes. That means I could attempt to speed of a platform backed by MySQL by adding an index, and all of a sudden break the application. How on earth could that be construed in any way other than retarded? This query can be made to work: code:
Try this: code:
|
# ? Dec 8, 2007 17:21 |
|
Victor posted:
This also returns the incorrect result (at least on mysql 5). I think you might just have to chalk this up to a mysql bug since sqlite and postgres seem to do this correctly. Create the index and, ideally, compare database versions somewhere and die if its not your current one.
|
# ? Dec 8, 2007 17:53 |
|
Try the following:code:
|
# ? Dec 8, 2007 18:05 |
|
Sorry guys I thought I replied, but apparently not. I seem to have solved it by running this:code:
|
# ? Dec 9, 2007 17:20 |
|
|
# ? May 13, 2024 11:33 |
|
Victor posted:noonches, you have to be kidding me. You're advocating an RDBMS which returns different results based on configuration like indexes. That means I could attempt to speed of a platform backed by MySQL by adding an index, and all of a sudden break the application. How on earth could that be construed in any way other than retarded? AFAIK there are MySQL options that can disable most if not all of the retardedness. Unfortunately it suffers from the same problem as PHP where there are a ton of applications out there built with the assumption of retarded behavior. Obviously these break when the system is configured in a sane manner, so the defaults and settings commonly used on shared hosts are insane. It's a bad cycle too, since leaving that crap enabled just means more idiots will use it, meaning there are even more applications which depend on bad features, and so on.
|
# ? Dec 9, 2007 21:03 |