|
Well the problem I guess is really more like the following:code:
Edit: Where I'm going here is that the problem is being misrepresented as a GROUP BY/DISTINCT problem and really it's a modeling problem. It sounds like you'd like to be able to GROUP BY user OR ip, which is impossible. This query might be possible but I think it's going to be exceedingly ugly. npe fucked around with this message at 19:10 on Mar 26, 2008 |
# ? Mar 26, 2008 19:04 |
|
|
# ? May 13, 2024 07:16 |
|
yaoi prophet posted:
The rows also have a date function. Begin by accepting the most recent entry. Start going back in time, and accept all entries except those matching either a username or an IP. So in this case code:
quote:This query might be possible but I think it's going to be exceedingly ugly. Yes, I'm going to give up doing this in MySQL. It's simply not equipped for the job.
|
# ? Mar 26, 2008 19:13 |
|
functional posted:Yes, I'm going to give up doing this in MySQL. It's simply not equipped for the job. I think you might be making the right choice, but I'm not sure it's MySQL's fault. You'd be better off trying to determine some of this by evaluating it in a script or changing your table structure.
|
# ? Mar 26, 2008 19:19 |
|
functional posted:Yes, I'm going to give up doing this in MySQL. It's simply not equipped for the job. You'd have a hard time pulling this off in any database, because the modeling is bad. You have a sort of entity defined based on one or the other attribute, that would more appropriately be represented by a related table.
|
# ? Mar 26, 2008 19:27 |
|
yaoi prophet posted:You'd have a hard time pulling this off in any database, because the modeling is bad. You have a sort of entity defined based on one or the other attribute, that would more appropriately be represented by a related table. We come from different schools of thought. I've learned that you should only store actual pieces of information (ip, time of creation), and store nothing else that can be computed on the fly. Relations between cells, for instance. Where I come from that's good modeling. It has the advantage that you can always trust what's stored, and that you never generate cruft. It has the disadvantage of running into query times that are not always negligible. When you assume your database is in memory, most query times are negligible, and then the database systems that support these methods offer real advantages. It's also not the only way to do things. There are many acceptable ways.
|
# ? Mar 26, 2008 19:52 |
|
I might misunderstand the issue at hands, but can't the issue be divided in two? First you need the IP to only be able to vote once; so you make a query that get the vote from unique IP, getting the first of every IP. Then you want unique user, the first user user from each of those unique IP.. I ended up with that;code:
|
# ? Mar 26, 2008 20:45 |
|
If I have a CSV file that matches up perfectly with a table that has FK values, how do I input that data? Do I have to hand edit it to take out the foreign key values, or is there a simple work around in SQL? (I am brand new to SQL, just learned how to create tables. Go easy on me.)
|
# ? Mar 27, 2008 01:44 |
|
Why is this:code:
Yet this: code:
I'm trying to get it to display only one instance of each post_id but my first query only returns all the tag_id's for one post_id.
|
# ? Mar 27, 2008 02:05 |
|
Tots posted:If I have a CSV file that matches up perfectly with a table that has FK values, how do I input that data? Are you using BULK INSERT (if this is SQL Server)? You could disable the FK constraint temporarily, load the data, then null any invalid FK values out (UPDATE newTable SET foreignKey = NULL WHERE foreignKey NOT IN (SELECT primaryKey FROM oldTable) or something like that) and re-enable the constraint.
|
# ? Mar 27, 2008 02:21 |
|
drcru posted:Why is this: DISTINCT is not a function, so it cannot actually be applied to a single return column, despite how it looks. code:
You'll need to reword what you're looking for - maybe "for each postid the tag of the postid with the largest date". noonches posted an example of a solution to this kind of problem higher up on this page.
|
# ? Mar 27, 2008 02:31 |
|
Ardhanari posted:Are you using BULK INSERT (if this is SQL Server)? You could disable the FK constraint temporarily, load the data, then null any invalid FK values out (UPDATE newTable SET foreignKey = NULL WHERE foreignKey NOT IN (SELECT primaryKey FROM oldTable) or something like that) and re-enable the constraint. I'm using the school's database server, which currently doesn't give me permission to do a BULK INSERT, but I am working on getting that permission. For now, I am using sed (a search and replace program) to add in "INSERT INTO MyTable VALUES (" before each line, and a ")" after each line. I know I could further use RegExp to manipulate the data to suit my needs, but I know even less about RegExp than I know about SQL. Now, to see if I understand what you are saying. I am basically going to set the Foreign Key columns as regular columns, then add in the data, then UPDATE the Foriegn Key columns, inserting the data from their parent table, then reset them as foreign keys?
|
# ? Mar 27, 2008 02:32 |
|
Tots posted:I'm using the school's database server, which currently doesn't give me permission to do a BULK INSERT, but I am working on getting that permission. For now, I am using sed (a search and replace program) to add in "INSERT INTO MyTable VALUES (" before each line, and a ")" after each line. I know I could further use RegExp to manipulate the data to suit my needs, but I know even less about RegExp than I know about SQL. Yeah. See here under 'Disabling Foreign Key Constraints'. You'll basically just be turning off the check for data integrity while you insert the new rows.
|
# ? Mar 27, 2008 06:08 |
|
I'm doing a small php+sql project for a database course. I thought everything went well until I started adding checks for false data injection like negative numbers to time column and so on. Here's an example. CREATE TABLE TEST ( NUMBER1 INT NOT NULL, NUMBER2 INT CHECK ( NUMBER2 < 10 ), PRIMARY KEY ( NUMBER1,NUMBER2 )); mysql> describe test; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | number1 | int(11) | NO | PRI | | | | number2 | int(11) | NO | PRI | 0 | | +---------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) Then, I add some data (false data!): mysql> insert into test(number2) values (100); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from test; +---------+---------+ | NUMBER1 | NUMBER2 | +---------+---------+ | 0 | 100 | +---------+---------+ 1 row in set (0.00 sec) My question is - what the gently caress? I thought the CHECK was supposed to make sure I cannot add any int value to number2 which is bigger than 9. I also thought I cannot add anything to the table without a not null value for number1. There is th warning, but the the database still accepts it - why? What am I missing?
|
# ? Mar 27, 2008 12:25 |
|
Regarding the check constraint, the mysql manual says:quote:The CHECK clause is parsed but ignored by all storage engines. For the NOT NULL, I believe this is relevant: quote:Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values and coerces them to legal values for data entry. In MySQL 5.0.2 and up, that remains the default behavior, but you can change the server SQL mode to select more traditional treatment of bad values such that the server rejects them and aborts the statement in which they occur.
|
# ? Mar 27, 2008 13:33 |
|
I have kind of a general but easy newb question - I'm looking to represent a large number of strings in a table. If I could compress them to ints (by a 1:1 hashing mechanism), would this improve searching time on the table? I've been thinking a lot about it (and I have very little SQL experience) but ultimately it comes down to: is a single varchar comparison slower than a single integer comparison? What if the strings were stored in fixed size char arrays?
|
# ? Mar 28, 2008 19:40 |
|
Clobbersaurus posted:I have kind of a general but easy newb question -
|
# ? Mar 28, 2008 19:58 |
|
minato posted:Integer lookups are significantly faster than string lookups - but if your database engine's index system is worth it's salt, it'll be hashing any strings into integers behind the scenes, so there's probably little point in doing it yourself. It's MS Sql, so it probably does - is there a guide to database optimization that would cover this sort of thing somewhere? I'll probably run into similar problems later too. If anyone knows any online resources on this I'd love to see them.
|
# ? Mar 28, 2008 20:18 |
|
My boss wants a table in our MySQL database that stores customer contracts in pdf format. Is this workable? I found this, http://www.perlmonks.org/?node_id=150255 , which applies directly to me (LAMP environment [Perl]), but I'm still not sold that it's a good idea. LightI3ulb fucked around with this message at 21:43 on Mar 28, 2008 |
# ? Mar 28, 2008 21:18 |
|
LightI3ulb posted:My boss wants a table in our MySQL database that stores customer contracts in pdf format. Is this workable? What I believe you want (although I have never coded it myself) is a binary large object (BLOB). I scanned the article you posted and it was talking about mySQL 4.0.1, which is outdated. I would suggest to try using just a regular BLOB data column and check the performance before going for some fancy solution. Hopefully this should get you started: http://www.google.ca/search?hl=en&client=firefox-a&rls=com.ubuntu%3Aen-US%3Aofficial&hs=IM1&q=blob+pdf+file+mysql&btnG=Search&meta= chocojosh fucked around with this message at 22:25 on Mar 28, 2008 |
# ? Mar 28, 2008 22:21 |
|
You should think about whether or not you want the actual PDF's in your database. The alternative is to not store the actual files, but to load them to a more typical file server running a webserver, and then store the path to the file in the database. This is a bit of a religious war where I work, but the long and the short of it is that these two approaches have (as always) advantages and disadvantages: In the database: Advantages - data integrity (your pdf's won't get deleted by a wayward shell script) - ease of retrieval (just use the database connection, select the BLOB, presto!) Disadvantages - bloats the size of your table, can't be easily divided into multiple storage nodes without dealing with some form of database partitioning/clustering or using some form of clustered filesystem/san - forces all requests for the file to be served by the database, turning your database into a file server, which it's not really built for Outside the database: Advantages - more easily managed storage of files, can be done across multiple storage servers with no trouble - keeps your tables normal sized - requests for the file can be served over something like apache, which is built for exactly that Disadvantages - no data integrity enforced by the database (rm -rf *, oh poo poo) - can't just SELECT your file out, have to SELECT out the location and then retrieve over http, which is sometimes a little more complicated depending on what you're doing
|
# ? Mar 29, 2008 02:18 |
|
yaoi prophet posted:You should think about whether or not you want the actual PDF's in your database. The alternative is to not store the actual files, but to load them to a more typical file server running a webserver, and then store the path to the file in the database. This is a bit of a religious war where I work, but the long and the short of it is that these two approaches have (as always) advantages and disadvantages:
|
# ? Mar 29, 2008 20:20 |
|
Xae posted:I prefer to keep the min the DB, because I am a DB whore. The ease of access on a database can be improved by using stored procedures to create a hybrid system. You can have one procedure that when passed the name(PK) of a file, and a location will write out the file to that location for easy pick up. A complementary procedure can be used to replace the blob in the database with the one at the specified location. Well, that solves some cases but not all. In our case this wouldn't solve the problem, because you are still left with the critical disadvantages of having all of your file data being served at some point via the database (even with your solution, which just adds another layer, it doesn't really take out the bottleneck), which means pushing a high volume of data through what is commonly an already overstressed component. It also means we'd be storing terabytes of file data as part of the database and it would make managing of table data exponentially more difficult. By storing the files on external SAN nodes we can trivially add more storage without database downtime. I have no idea what kind of needs LightI3ulb has, though. For something small it's probably no problem, but I wanted to point out that this is frequently a hotly contested design point. At my office we refer to people who are "innies" vs those who are "outies".
|
# ? Mar 29, 2008 21:55 |
|
yaoi prophet posted:Well, that solves some cases but not all. In our case this wouldn't solve the problem, because you are still left with the critical disadvantages of having all of your file data being served at some point via the database (even with your solution, which just adds another layer, it doesn't really take out the bottleneck), which means pushing a high volume of data through what is commonly an already overstressed component. It also means we'd be storing terabytes of file data as part of the database and it would make managing of table data exponentially more difficult. By storing the files on external SAN nodes we can trivially add more storage without database downtime. You should be able to partition the table, vertically if the volume of the files is too much for your database. Split the tablespace and have the blob table on a separate set of disks.
|
# ? Mar 29, 2008 23:33 |
|
Xae posted:You should be able to partition the table, vertically if the volume of the files is too much for your database. Split the tablespace and have the blob table on a separate set of disks. Partitioning brings it's own set of problems (we partition extensively currently). Storing 350tb of files inside the tables is not really a serious option even to our most rabid advocates of that approach.
|
# ? Mar 30, 2008 01:55 |
|
Clobbersaurus posted:It's MS Sql, so it probably does - is there a guide to database optimization that would cover this sort of thing somewhere? I'll probably run into similar problems later too. If anyone knows any online resources on this I'd love to see them. To the best of my knowledge MS SQL Server will not hash the strings and requires a full compare. However if there's an index on the column it can use it'll iterate over the string as it traverses the index, i.e. it'll use just enough of the string to differentiate it from the others. This means code:
code:
MySQL has hash indexes which are fast for single row equijoins, but slow for everything else.
|
# ? Mar 30, 2008 13:45 |
|
Zombywuf posted:I expect to only be doing exact string matches so I think it should be fast. Thinking more and more about it, it seems ridiculous that it wouldn't be able to do a binary search on a sorted table of strings.
|
# ? Apr 1, 2008 16:34 |
|
Well it uses B-Trees, branching factor of "a lot". This has the major advantage that similar data is kep together on the disk. Hash tables spread the data all over the place, leading to poor cache performance and your drives making a funny grinding noise.
|
# ? Apr 1, 2008 18:27 |
|
My query (below) takes about 2 seconds to complete in MySQL. Can anyone suggest methods for optimizing this? Are there any blatant, horrible mistakes that I've made? code:
Stephen fucked around with this message at 22:56 on Apr 1, 2008 |
# ? Apr 1, 2008 18:53 |
|
Stephen posted:My query (below) takes about 2 seconds to complete in MySQL. Can anyone suggest methods for optimizing this? Are there any blatant, horrible mistakes that I've made? MySQL doesn't support CTEs as far as I know, but SQL experts, would this query benefit from using CTEs, or does the optimizer treat a CTE the same as an inline table (if that's what you call search and details in the above query)? Would views be better to use? I don't know anything about benchmarking in mySQL either, but there has to be some kind of tool that shows the physical and logical reads and how much you are using the buffer cache. Generally the first time you run a query, your buffer cache hit ratio won't be the best, but the more you use the query, the more you should find in the cache, so when you benchmark your queries, benchmark them multiple times. It wouldn't be too hard to make the inline tables views, and see if that helps any. Edit: Oh yeah, also indexes... I assume you have proper indexes? Rumsfoord fucked around with this message at 01:39 on Apr 2, 2008 |
# ? Apr 2, 2008 01:24 |
|
Sorry if this has come up before, please just tell me if it has. In SQL Server 2000, I have a table like this:code:
code:
|
# ? Apr 2, 2008 04:17 |
|
I can't think of a way to do it without a subquery:code:
|
# ? Apr 2, 2008 04:52 |
|
minato: Thanks for your time. I just thought there may have been a more elegant way - it seems as though there should be.
|
# ? Apr 2, 2008 06:31 |
|
Could someone please help me come up with a WHERE clause that returns a row if it's NOT in the form of "XXX-XXX" for instance, "abc-abc" is not a hit, but "abc-xyx" is. The column can be written as "abc-abc-asd-fdffsdfrekl kjfdg", but I'm only interested in the first seven characters. I imagine regular expressions could play a role in this, but I don't know how to do those edit: changed requirements nbv4 fucked around with this message at 10:32 on Apr 2, 2008 |
# ? Apr 2, 2008 10:22 |
|
nbv4 posted:Could someone please help me come up with a WHERE clause that returns a row if it's NOT in the form of "XXX-XXX" code:
|
# ? Apr 2, 2008 11:13 |
|
Zombywuf posted:
ah, good job. I didn't realize sql had substring functions. I was looking around for a fulltext search solution...
|
# ? Apr 2, 2008 11:37 |
|
I'm trying to join two tables, one which contains a few special items, and another which contains all potential items and all their associated columns. Unforutantely I can't just use a primary key to join them on, I have to use these three columns. What I have here totally doesn't work, but I hope it at least describes what I'm trying to do. I want to join the two tables on one column if that's all that's available and on another, and a third, if they are available to be joined upon. Hurts my just trying to explain it. Any ideas? fake edit: I'm using T-SQL with SQL Server 2005 code:
Above is what I was the direction I've been working in. Below is what I have right now, and it executes wihtout any errors that I can see, but doesn't return anything. code:
The best solution was to simply create records in the second table that represented lone, top level, and mid level categories and to make sure to just always have all three keys. Cazlab fucked around with this message at 16:56 on Apr 2, 2008 |
# ? Apr 2, 2008 15:03 |
|
Rumsfoord posted:MySQL doesn't support CTEs as far as I know, but SQL experts, would this query benefit from using CTEs, or does the optimizer treat a CTE the same as an inline table (if that's what you call search and details in the above query)? I tried added an index to the view_date, but that just hosed up my queries all together. SELECTS weren't returning any results after that. Edit: Holy poo poo, after playing with the query for a few minutes, I changed the INNER JOIN to a LEFT JOIN and it shaved 2 seconds off the query. What the gently caress? Stephen fucked around with this message at 16:39 on Apr 2, 2008 |
# ? Apr 2, 2008 16:18 |
|
I have an existing query that the boss wants me to add a column to:code:
I have no idea how to make it do this in the current query. Any help is greatly appreciated. It's in MS SQL Server 2000.
|
# ? Apr 2, 2008 20:15 |
|
dest posted:I have an existing query that the boss wants me to add a column to: code:
|
# ? Apr 2, 2008 20:39 |
|
|
# ? May 13, 2024 07:16 |
|
Begby posted:
I copied that, added the correct FROM, and I got the error: "Didn't expect 'when' after the select column list"
|
# ? Apr 2, 2008 22:42 |