|
dest posted:I copied that, added the correct FROM, and I got the error: "Didn't expect 'when' after the select column list" Remove the A.UnitPrice from just before the WHEN. CASE [Column] WHEN takes a value, CASE WHEN... takes a conditional expression. You need the conditional since it's not an exact match. Also you should be able to set the ELSE value to NULL if you don't want it populated with a zero.
|
# ? Apr 2, 2008 23:39 |
|
|
# ? May 13, 2024 08:42 |
If I'm trying to optimize a query that has joins on it, am I supposed to (multicolumn)index the fields I'm joining on? Or just the fields in the WHERE? What about the field I'm doing an order by on?
|
|
# ? Apr 3, 2008 01:55 |
|
"FROM foo JOIN bar ON (condition)" is equivalent to "FROM foo, bar WHERE (condition)". The main reason for JOIN's existence is to help separate the table relationships from the rest of the filtering conditions. With regards to indexing, this is what the EXPLAIN statement is for (if you can interpret the often cryptic results). A general rule of thumb is that if the DB engine has to perform a sequential scan on a large table, then you probably want an index on the column(s) it's using. Don't forget about multi-column indices. E.g. if you have a table like "student_class (student_id, class_id, ...)" and you're looking up by both student_id and class_id simultaneously, then it may be faster to add an index on (student_id, class_id) than to have two separate indexes on student_id and class_id separately. Adding indexes on ORDER BY columns generally hasn't helped at all in my experience.
|
# ? Apr 3, 2008 02:52 |
minato posted:<wisdom> So will I end up having columns that are indexed multiple times depending on how I'm looking up the data? Like: indexA: (first_name, last_name) indexB: (birthday, last_name)
|
|
# ? Apr 3, 2008 03:45 |
|
fletcher posted:So will I end up having columns that are indexed multiple times depending on how I'm looking up the data? Yes. When in Doubt check out Postgres' Docs http://www.postgresql.org/docs/8.3/static/indexes-bitmap-scans.html http://www.postgresql.org/docs/8.3/static/indexes-multicolumn.html Xae fucked around with this message at 04:19 on Apr 3, 2008 |
# ? Apr 3, 2008 04:17 |
|
Fun little problem. Let's say I have following tables: --- flower_bees <= 2 join tables with flower_id, and bee_id in both of them bee_flowers -- bees -- I need to grab all bees that are found in both flower_bees and bee_flowers. I have a solution using Union, but wondering if there's a nicer way
|
# ? Apr 3, 2008 14:52 |
|
GroceryBagHead posted:--- code:
|
# ? Apr 3, 2008 15:06 |
|
GroceryBagHead posted:Fun little problem. If I understand your problem, this is what I would do: code:
|
# ? Apr 3, 2008 15:08 |
|
Alex007 posted:If I understand your problem, this is what I would do: Sorry, I need to clarify: I need bees that are in flower_bees OR bee_flowers.
|
# ? Apr 3, 2008 15:19 |
|
GroceryBagHead posted:Sorry, I need to clarify: I need bees that are in flower_bees OR bee_flowers. code:
|
# ? Apr 3, 2008 15:24 |
|
GroceryBagHead posted:Sorry, I need to clarify: I need bees that are in flower_bees OR bee_flowers. Then LEFT OUTER JOIN is your friend. If you do "A LEFT OUTER JOIN B" then you still get all the columns of B regardless of if the join succeeds, but they're just all null if the join criteria didn't match anything on B. code:
|
# ? Apr 3, 2008 15:25 |
|
I'm tyring to reinstall mysql on a linux (2.6.18-1.2798.fc6) server that's had several administrators before me. I've cleaned out the old installation, and I reinstalled the latest version. After creating the mysql group, I used : mysql_install_db --user=mysql mysqld_safe --user=mysql & When I tried to set the admin pasword with mysqladmin -uroot password 'secret' , it gives me connect to server at 'localhost' failed error: access denied for user 'root'@'localhost' (using password: NO) I've run ps -e | grep mysql and ps -e | grep httpd and they both come back showing an active server, which the error logs seem to agree. Anyone have any idea what could be a possible source, or a way to make the error logs more helpful? I realize this is probably more of a server question that an SQL question, but I figured quite a few people here may have run into something similar before.
|
# ? Apr 4, 2008 02:43 |
|
I solved the problem! Turns out there was a (possible several ) previous installation(s) of mysql in custom directories. Since the previous root user had left, I was able to reset the root password by using mysqld_safe with the --skip-grant-tables option, and then logging on and changing the root password in the user table. Let this also serve as a reminder to never let anyone have access to the daemon unless they're the root user for the database.
|
# ? Apr 4, 2008 15:38 |
|
I inherited this really gross search query that is over 120 lines of SQL mixed with coldfusion. It is from a job search application I am responsible for maintaining and the query is shared by the administration view and the job search view for people looking for jobs. Anyways we have slow query logging turned on in mySQL (set at 4+ seconds), and after about 3 months of this database being moved to MYSQL (it was hosted on Access before!), I have a 21MB log file that just has this query in it. Right now on our live website a keyword search is done using a %LIKE%, which is ridiculous because it is so slow, and if you want a job programming PHP and search PHP you get all the PHP jobs + all the jobs that have URL's that have .php in them so https://www.blah.com/whatever.php?var=xx comes up as a valid PHP job. So I read about the wonderful world of FULLTEXT indexes and went on implementing it, but I can't get the mother fuckers to work properly. I keep making a FULLTEXT index with like 5 different columns from the job_description table and when I go to run the query it says I don't have a FULLTEXT index with that name but as I soon as I add "IN BOOLEAN MODE" it works. But it misses a lot of data. So this doesn't work: code:
code:
|
# ? Apr 5, 2008 00:37 |
|
I have a question about database design. I'm not sure if it should be here but I would prefer to avoid making a separate thread. Essentially, if you have two database tables (entities) who share a relationship with cardinality 1 to N, where N is a positive number, what do we do? For example, I built a website for a trip I am organizing. Users can make an account and enter which hotel they want, which bus they want, and if they want certain extras. They pay in person at our student club's office, and then I enter the payment information into the database also. I know that there is going to be a maximum of four people in any hotel room (and obviously a minimum of one). Thus, I made a table Room with fields Person1, Person2, Person3, Person4 where those four fields store the username of the appropriate user. I can see how this is bad design and very inflexible. However, the system is meant to be updated once a year (as we offer the trip once a year to our members) and I figured it was more important to get the site done then worry about "future-proofing". However, I was wondering what better solutions there are (make a joining table called UserRoom that would have a trigger on insert to make sure no more than 4 people are in a given room?)
|
# ? Apr 5, 2008 07:57 |
|
Join table is the way to go and let your application handle validation of how many people you want to cram per room (maybe it will be 6 next year)
|
# ? Apr 6, 2008 03:10 |
|
chocojosh posted:Essentially, if you have two database tables (entities) who share a relationship with cardinality 1 to N, where N is a positive number, what do we do? The normal thing to do for 1 to N relationships is: code:
quote:However, I was wondering what better solutions there are (make a joining table called UserRoom that would have a trigger on insert to make sure no more than 4 people are in a given room?) code:
|
# ? Apr 6, 2008 09:51 |
|
GroceryBagHead posted:Join table is the way to go and let your application handle validation of how many people you want to cram per room (maybe it will be 6 next year) I think for next year I'm going to have to update the design with a better table schema.. I've had to write a few reports two nights ago and the code was quite bad: (User U JOIN Room R ON U.UserName = R.Person1 OR U.UserName = R.Person2 OR U.UserName = R.Person3 OR U.Username = R.Person4). The current table schema has met my needs for this year though, so at least I got something working on my first try Zombywuf posted:Just say 'no' to triggers. Can you give me some reading material as to why? At my job the DBAs do not use triggers (I was told we have one in the entire application). I'd really like to know more about any dangers/problems with triggers.
|
# ? Apr 6, 2008 15:17 |
|
Don't have any reading material to hand, but google around a bit and you'll find a lot of trigger related horror stories. Basically, it's very easy to get yourself into a mess with triggers. A simple case to illustrate would be:
Another way of looking at it is that triggers are almost exactly like Intercal's COME FROM statement or Aspect Oriented Programming.
|
# ? Apr 6, 2008 16:56 |
|
chocojosh posted:Can you give me some reading material as to why? At my job the DBAs do not use triggers (I was told we have one in the entire application). I'd really like to know more about any dangers/problems with triggers. Using triggers for business logic = Bad. Why? Because it is hidden. And if it is hidden it is forgotten. And unless you are home to the world's most careful programmers who always check the documentation, you will get burned. It is far better design to have an explicit function that will update and manage complex relationship then it is to hide the process in a dozen triggers. One of my previous employer abused triggers. Almost all logic was performed in triggers. It was to the point where no one really knew what was supposed to happen. It just did. Except when it didn't. Then all hell broke lose because no one knew how something was supposed to be handled. And you had to jump through 4 triggers a statement, some of which altered other tables, meaning you had then jump through 4 more triggers, and it goes on and one.
|
# ? Apr 6, 2008 17:12 |
|
var1ety posted:Oracle has a CONNECT BY operator that lets you do this. This worked perfectly until I encountered a query that was expecting over 31000 results. The query essentially just sits there. I tried looking at your link, but the mysql keywords aren't compatible with Oracle 10g. Can you (or anyone) steer me towards something that might be workable? Since this query worked until it reached a certain query that needed too many results, I wonder if there is a way to break the query up. SQL/querying doesn't seem too hard of a concept, but now that I dig in here, I give you guys a lot of credit for the possible complexity in situations where nesting and over conditions are a concern. Thanks in advance for any input. My original post on this was on page 13 (right in the middle).
|
# ? Apr 7, 2008 20:04 |
|
Question for the DBI experts out there. I cant figure out why this:code:
2008-03-31 2008-04-01 2008-04-02 2008-04-03 2008-04-04 2008-04-05 2008-04-06 2008-04-07 2008-03-31 2008-04-01 2008-04-02 2008-04-03 2008-04-04 2008-04-05 2008-04-06 2008-04-07 2008-03-31 2008-04-01 2008-04-02 2008-04-03 2008-04-04 2008-04-05 2008-04-06 2008-04-07 2008-03-31 2008-04-01 2008-04-02 2008-04-03 2008-04-04 2008-04-05 2008-04-06 2008-04-07 but this: code:
mysql> select date(convert_tz(mcl.call_date, 'US/Eastern', 'US/Central')) call_date, m.product_id, mcl.source, count(*) quantity from membership_call_log mcl, memberships m where mcl.membership_id=m.membership_id and datediff(mcl.call_date, curdate()) <= 45 group by mcl.source, m.product_id, date(mcl.call_date); +------------+------------+---------+----------+ | call_date | product_id | source | quantity | +------------+------------+---------+----------+ | 2008-03-29 | 1 | | 1 | | 2008-03-29 | 1 | DIRECT | 2 | | 2008-04-01 | 1 | DIRECT | 1 | | 2008-03-29 | 1 | USBI | 1 | | 2008-04-04 | 1 | UNKNOWN | 4 | +------------+------------+---------+----------+ 5 rows in set (0.00 sec) mysql> select date(mcl.call_date) call_date, m.product_id, mcl.source, count(*) quantity from membership_call_log mcl, memberships m where mcl.membership_id=m.membership_id and datediff(mcl.call_date, curdate()) <= 45 group by mcl.source, m.product_id, date(mcl.call_date); +------------+------------+---------+----------+ | call_date | product_id | source | quantity | +------------+------------+---------+----------+ | 2008-03-29 | 1 | | 1 | | 2008-03-29 | 1 | DIRECT | 2 | | 2008-04-01 | 1 | DIRECT | 1 | | 2008-03-29 | 1 | USBI | 1 | | 2008-04-04 | 1 | UNKNOWN | 4 | +------------+------------+---------+----------+ 5 rows in set (0.00 sec) mysql> Same output LightI3ulb fucked around with this message at 00:38 on Apr 8, 2008 |
# ? Apr 8, 2008 00:34 |
|
LightI3ulb posted:
Are you not getting any errors when running the query through Perl? There was a (small!) difference between the query you ran directly in the mySQL command interface and through php. At the very end of the query: m.product_id, date(call_date) call date isn't mcl.call_date, rather plain call_date. In all working examples, the alias is used in the reference. I don't know if that's the problem, just something I noticed!
|
# ? Apr 8, 2008 02:14 |
|
No errors at all, and adding mcl doesn't change anything
|
# ? Apr 8, 2008 02:29 |
|
LightI3ulb posted:No errors at all, and adding mcl doesn't change anything If you put some output after the sql execution/while loop (print "i got here!") does it show up?
|
# ? Apr 8, 2008 02:32 |
|
Yep
|
# ? Apr 8, 2008 02:58 |
|
So no one has ever had a problem with MySQL and creating a fulltext index on a table and trying to use it and having MYSQL claim it is not there? although boolean mode works for some reason. I searched the web and I found someone who posted this problem in 2007 on the official mysql support forum and no one answered their either.
|
# ? Apr 8, 2008 03:52 |
|
LightI3ulb posted:No errors at all, and adding mcl doesn't change anything Did you connect() with RaiseError? You aren't checking that execute() succeeded. Exceptions and DBI go well together: code:
|
# ? Apr 8, 2008 04:17 |
|
da keebsta knicca posted:So no one has ever had a problem with MySQL and creating a fulltext index on a table and trying to use it and having MYSQL claim it is not there? although boolean mode works for some reason. Post the table schema, and the EXPLAIN output for whatever queries are not acting as expected. MySQL fulltext is quite limited, and its possible you are producing a query where the index cannot be used as you are expecting because it is mixing another index in. More than likely, your boolean mode query is not using the index either (they can complete without one, but its slow).
|
# ? Apr 8, 2008 04:27 |
If you have a users table and an articles table, what do you guys name the column that links the article to the user? 'userId' or something more descriptive like 'authorId'? edit: I guess I should note this is for MySQL/MyISAM table, if that makes any difference. fletcher fucked around with this message at 06:23 on Apr 8, 2008 |
|
# ? Apr 8, 2008 06:13 |
|
fletcher posted:If you have a users table and an articles table, what do you guys name the column that links the article to the user? 'userId' or something more descriptive like 'authorId'?
|
# ? Apr 8, 2008 06:24 |
|
minato posted:Something more descriptive. Because you can be sure that eventually you'll have fields like "Created by", "Last updated by" and "Maintained by" that will all refer to user IDs, so you can't just use userId for those. I'd name it UserID for consistency in the foreign key relationship (User.UserID->Article.UserID reads better than User.UserID->Article.AuthorID), and those others would be CreatedBy, ModifiedBy, etcetera -- and those could be either IDs or user names, I've seen both used. Of course, it's all pretty much up to how quickly you want other people to pick up on what you did
|
# ? Apr 8, 2008 07:29 |
|
code:
code:
while (my $row = $sth->fetchrow_hashref()) { $records{$num_records}=$row->{'call_date'}; $date = ParseDate($records{$num_records}); $records{$num_records}=Date_ConvTZ($date, 'US/Eastern', 'US/Central'); $prod_id{$num_records}=$row->{'product_id'}; $source{$num_records}=$row->{'source'}; $quantity{$num_records}=$row->{'quantity'}; $num_records++; } LightI3ulb fucked around with this message at 13:51 on Apr 8, 2008 |
# ? Apr 8, 2008 13:23 |
|
I have two tables - the first one has a bunch of company information and user#s. The second table is strange, as it has a bunch of rows I need formatted as columns in the first table. I need one query to rule them all. I'd like to run the query and get something that resembles the Result Table. EDIT: This will be run on MySQL. code:
Rakshas fucked around with this message at 14:09 on Apr 8, 2008 |
# ? Apr 8, 2008 14:05 |
|
Rakshas posted:I have two tables - the first one has a bunch of company information and user#s. The second table is strange, as it has a bunch of rows I need formatted as columns in the first table. I need one query to rule them all. I'd like to run the query and get something that resembles the Result Table. What platform is this gonna run on ? SQL 2005, SQL 2000, MySQL, Postgres ?
|
# ? Apr 8, 2008 14:09 |
|
Alex007 posted:What platform is this gonna run on ? SQL 2005, SQL 2000, MySQL, Postgres ? Will be run on MySQL.
|
# ? Apr 8, 2008 14:09 |
|
I'm making progress on my own problem. Left join should work for one field#, but how to combine all of them into one? I'd rather not perform 3 left joins and combine the information manually. code:
Rakshas fucked around with this message at 16:11 on Apr 8, 2008 |
# ? Apr 8, 2008 15:23 |
|
Begby posted:
Thanks, that helped!
|
# ? Apr 8, 2008 22:17 |
|
genericadmin posted:Post the table schema, and the EXPLAIN output for whatever queries are not acting as expected. MySQL fulltext is quite limited, and its possible you are producing a query where the index cannot be used as you are expecting because it is mixing another index in. More than likely, your boolean mode query is not using the index either (they can complete without one, but its slow). I have deleted a bunch of indexes and cleared everything out still no go with this simplified SQL. code:
code:
|
# ? Apr 9, 2008 01:17 |
|
|
# ? May 13, 2024 08:42 |
|
da keebsta knicca posted:....sql stuff... This is kinda shooting from the hip, but have you tried this? code:
code:
code:
|
# ? Apr 9, 2008 02:13 |