| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Comments:
<0> CREATE PROC makes my cock like rock!
<1> the error I get ONLY on the bottom two queries is this: Unknown column 'listingid' in 'where clause'
<1> the column listingid exists, but perhaps it is because the table has nothing in it?
<2> munc: bzzt, incorrect. now paste the output from DESC bids; and DESC listing;
<1> Hmm I'm sorry?
<2> paste the output from "DESC bids;" and "DESC listing;"
<1> I'm not sure what ya mean, I'm a major noob.. you want me to add the DESC before bids and listing in my query?
<3> DESC is equivalent to DESCRIBE
<2> I want you to log into the mysql client, run those two queries above (without the quotes), and paste the output.
<1> alright one sec
<1> #1054 - Unknown column 'listingid' in 'where clause' for images (are you sure its not because the table is completely empty?)
<4> positive
<2> I mean.
<2> DESC bids;
<2> DESC listing;
<2> RUN THOSE.
<3> use db first :)
<2> indeed.
<5> Being that new, he might not understand that either.
<1> You have to choose at least one column to display?
<0> who do you think should pay for a first date?
<1> Showing rows 0 - 30 (31 total, Query took 0.0039 sec)
<1> SQL query: DESC listing;
<5> ChrisKnig: Before or after you know how the night ends?
<2> munc: is your database server local or is it on a web host somewhere?
<1> local atm
<1> you want to connect?
<2> munc: ok. drop to a shell (or a cmd.exe prompt) and run "mysql -u USERNAME -p DATABASENAME"
<5> Drop and give us 20.
<1> infi: alright I'm in
<4> 20 from the atm?
<2> munc: type "DESC bids;" <ENTER> and copy the output. now type "DESC listing;" <ENTER> and copy that output also.
<5> Pushups.
<2> take the output of both of those, and put them on a pastebin (hashmysql.org, as you did before)
<2> preferably together, but at this point I don't care.
<0> Xgc: so if you don't like the person, you split the bill
<5> ChrisKnig: Make them pay.
<1> http://hashmysql.org/paste/viewentry.php?id=1522
<0> Xgc: haha, well what if it's not so much you don't like them but you just don't really want to see them again?
<2> munc: ok, those two tables (listing and bids) do not have a "listingid" column, therefore you cannot select it.
<0> god, this is making me hungry
<0> hungry for fish!
<0> bear need protein
<0> RAAARRR!!!!
<1> infi: bids does?
<1> it should be right below id
<2> oops.
<5> bids has a listingid. listing has an id, not listingid. He's a little confused about the errors.
<1> and for listing I'm only checking WHERE id = $q
<2> no, you're not: $query_Images = "SELECT * FROM listing WHERE listingid = $q LIMIT 1";
<1> I'm almost positive its because the image table is empty?
<5> munc: One of your queries is looking for listingid when it shouold be using id.
<1> http://hashmysql.org/paste/viewentry.php?id=1521
<5> munc: We saw that. You need to read is more carefully.
<5> s/is/it
<1> It has to be because the tables empty :P Because if I p*** Q=54 or whatever (something that doesn't exist in either of the 3 tables) I will only get errors when I query the image table
<5> munc: Being such a newbie, you might want to stop trying to tell us we're wrong. You might find the answer faster.
<1> for example, I can p*** listing_detail.php?q=54 and while it doesn't exist in either the listing or bids table, I will not get an error until I query images
<5> munc: The listing table does not have a listingid. Check that 3rd query.
<1> Oh wow, I see.. how stupid of me!
<5> munc: Then recheck the 2nd query. You didn't tell us the right error. It has to be something else.
<2> munc: http://hashmysql.org/paste/viewentry.php?id=1523
<1> I see now, in $query_Images I accidently typed SELECT * FROM listing when I should have had SELECT * FROM images
<2> you don't get errors when the table is empty, or when your where clause filters all records out. you just get 0 results.
<1> I'm retarded, sorry to waste your peoples time
<1> alright, thanks a ton infi and Xgc
<2> cheers
<5> munc: We live for this stuff.
<1> Okay another quick Q while I'm here.. is there any way to select something from another table when I'm querying say listings? Like can I select the highest number from a field called bids in the table bids while querying listing?
<2> of course. select t1.field1,t2.field2,t1.field3, ... t1.fieldN from table1 t1, table2 t2 where ...
<5> munc: Yes. It's called a JOIN.
<2> !m munc select
<6> munc: (SELECT Syntax) : http://dev.mysql.com/doc/mysql/en/SELECT.html
<3> !m munc tutorial
<6> munc: (MySQL Tutorial) : http://dev.mysql.com/doc/mysql/en/Tutorial.html
<3> better to start with this
<2> yeah, that too
<1> alright will read up
<7> can anyone recommend a good database theory book?
<7> or resource in general, i guess
<7> i want to layout my databases more intelligently
<8> !tell us about book
<6> will asked me to tell you this: MySQL by Paul Dubois: http://www.kitebird.com/mysql-book/
<7> not sepcifically howto mysql
<7> but about design theory
<8> oh
<7> mysql isnt hard to do following the docs
<7> what i dont know is the smartest way to do relation db's in general
<7> like, for programming, there's book on design patterns
<8> The way that works
<7> not sure what the analogue is for sql
<4> !google codd's 12 rules
<6> codd's 12 rules: http://www.itworld.com/nl/db_mgr/05072001/
<8> heh, some say, if you follow those rules, you shouldn't be using MySQL...
<8> Rules are overrated.
<4> !google sql normal forms
<6> sql normal forms: http://www.anaesthetist.com/mnm/sql/normal.htm
<7> there we go
<9> Hello. Is there a way to tell mysql to create an index on the resultset of a subquery? I've noticed that my subqueries are dead slow because mysql does full scans on their results.
<4> !man optimizing subqueries
<6> (Optimizing Subqueries) : http://dev.mysql.com/doc/mysql/en/Optimizing_subqueries.html
<3> pflanze: try to use join instead where possible
<9> lokus: I can't, since I need group by and having in the subqueries.
<3> pflanze: subquery sometimes are executed as required
<9> lokus: when I change my query involving two subqueries (and taking several seconds) to two create temporary table foo select ...; alter table foo add unique index(foofield); select ...,foo... then it is blinding fast.
<3> explain it and see what the optimiser do
<9> if I don't create the index, it is exactly as slow as the original query involving subqueries.
<9> so that's why I conclude that mysql doesn't create indizes on the subquerie's results (but still executes them separately first).
<3> it will never create index on subquery
<9> explain says that it scans, as far I can see.
<9> lokus: but it would solve my problem, afaict.
<9> mysql would be dull not doing it.
<3> pflanze: maybe you want to pastebin your query and see if anyone can give some idea
<9> http://hashmysql.org/paste/viewentry.php?id=1524
<5> hax: Try this one as well: http://www.tomjewett.com/dbdesign/dbdesign.php?page=intro.html&imgsize=medium
<9> This is the slow query (it takes 1.5 seconds with only a few hundred data entries).
<7> Xgc: you use uml?
<3> pflanze: can you try to put the subquery into the where instead of the from
<10> hey all
<3> pflanze: where StichwortVersion.Id = (select max(RowId) as StichwortVersionRID ...)
<9> lokus: ok I'll try.
<5> hax: Sure. You really don't need uml understanding for this.
<10> i am using mysql-query-browser, and one particular table has the "start editing" button disabled so i cant insert/delete entries. how can i enable editing on this table?
<7> Xgc: well, i'm also thinking about getting into the uml-way of doing things
<7> Xgc: it seems to be pretty well adopted these days, i figure those crazy guys at omg might be on to something :)
<7> Xgc: recommend any particular uml tool?
<3> uml rational ?
<7> i'm pretty sure that rational rose is very very expensive
<5> hax: This link contains a more complete description of some of the fundamental design issues. It's a good read for those who've read the brief overviews.
<3> hax: and it is under IBM now, i got an evaluation media sometime back but I naever use such tool
<9> lokus: hm, "ERROR 1242 (21000): Subquery returns more than 1 row"; I guess I'll have to +-completely rewrite my subqueries for this.
<3> pflanze: missed the group by , thought it was 1 row, try where StichwortVersion.Id IN (subquery)
<3> pflanze: but I guess this is not going to help
<9> lokus: ok now it gives correct results, but is even 4 times slower.
<3> pflanze: it is bad then, maybe converting to join could help but not sure if possible of not, but the IN usually can be converted to join, but the group by and max could make things complicated
<9> (Actually, that was with only one of the subqueries moved to where position, with both of them moved, it takes 14.4s now and is thus ~8 times slower.)
<3> pflanze: the subquery is now executed many times instead of just once when it is in the FROM
<9> Of all the ways suggested on the website to do such max stuff, I think (or thought) that subqueries were the fastest way when I started this.
<9> But the fun thing is, if I create temporary tables, and create an index on each of them, then do the final join, it is fast.
<9> I just hate having to rewrite my whole app for this.
<3> pflanze: maybe indexes on the 2 real tables could help
<3> pflanze: but still not good enough with 2 derived table that does not have index
<9> There are indexes on all columns involved in the query, afaict.
<9> I've double checked several times.
<3> pflanze: maybe can combined the 2 derived table into 1
<9> (I think that mysql simply executes that query like that: it internally rewrites them into creating temporary tables, then does the join; but it does not create an index on those result sets and thus the final join is slow.)
<3> probably
<4> it does a filesort though right?
Return to
#mysql
or
Go to some related
logs:
#perl
#perl
#physics
#gimp
#centos
#nvidia
#css
#perl
vlc plugin crashes fedora firefox
#python
|
|