| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Comments:
<0> good day. sql newb here. i have a table of table1.id, table2.id relations. I need to return records from table1.id that has two values in table2.id. Can I join on the same table twice or some such?
<0> something like select nid from term_node where tid = 19 AND tid = 1 ORDER BY nid DESC LIMIT 1;
<0> which, naturally, doesn't work.
<1> what about OR instead of AND? :)
<1> okay, you need both... erm. tid is a column in table2?
<0> well, because it the particular node needs to be ***igned to both tid 1 and tid 2.
<0> yes.
<0> term_node is just nid|tid
<2> hi all!
<1> then use OR anyways, group by the right columns, and use HAVING COUNT(*) = 2
<2> is there any way to detect if I have holes in a table?
<0> er, goddamit. i think i explained this wrong.
<0> I have nid:4|tid:5, nid:5|tid:5, nid:5|tid:6, and I need to be able to say "Gimme all the nids that have BOTH 5 and 6 tids" - in this example, 5.
<0> SELECT DISTINCT(node.nid), node.sticky, node.title, node.created FROM node INNER JOIN term_node tn0 ON node.nid = tn0.nid INNER JOIN term_node tn1 ON node.nid = tn1.nid WHERE node.status = 1 AND tn0.tid IN(1) AND tn1.tid IN(19);
<0> that appears to do it. is that Kosher, design/implementation wise?
<1> well, it could be done with my HAVING approach, too, I think.
<1> besides that, why not do it this way.
<1> IN(1) is nonsense, = 1 will do the trick :)
<0> heh.
<2> usually alot of logic like that is better suited for the application than in mysql
<0> RealRaven: so, multiple SQL statements are faster than 1?
<0> in this particular case, I mean.
<2> could be
<2> at least the sql-statements are easier to read
<3> Is it possible to do REGEXP in reverse? i.e. I have regular expressions stored in the database and want to see which records match a string I give.
<2> and the code will be maintainable
<1> SELECT node.nid, node.sticky, node.title, node.created, COUNT(*) FROM node INNER JOIN term_node tn ON node.nid = tn.nid AND tn.tid IN (1, 19) GROUP BY node.nid HAVING COUNT(*) = 2
<1> Morrye: multiple? why?
<1> err Morbus
<0> bigfoot-: hrm. that one is cleaner.
<0> I've never used HAVING before.
<1> Morbus: well, at least it saves one join. but I'm not sure whether it's faster.
<2> Morbus: HAVING is your friend :)
<1> MagicJohn: Yes.
<1> afaik and iirc.
<2> Morbus: how large are the tables?
<3> bigfoot-, may I ask how?
<0> RealRaven: at the moment, 1700 in each. Every new record grows one table by one, and the other table by 1 to 5.
<3> I've scoured the docs but at no avail
<1> MagicJohn: just the way you'd expect it to, reverse the REGEXP parameters
<2> Morbus: right, should be rather fast if you don't grow it to mush
<2> much
<0> yeah, growing it to say, 10,000 would take years
<0> .unless i did something really drastic.
<0> is my threshold 10,000, roughly?
<0> or 5,000? or 100,000? ;)
<3> So, 'SELECT * FROM `my_table` WHERE "mystring" REGEXP recordname' ?
<2> as long as you have indexes it will be fast forever
<1> MagicJohn: Yes.
<0> yes, i have indexes.
<0> okeedoo, thanks.
<2> I think your safe then :)
<3> Okay, thanks a lot :-)
<1> RealRaven: well. joins are always costly
<2> nobody know about "holes" in tables?
<4> Morbus: and if you believe that, I have some swamp land in Florida to sell you. :) Your work is never done. Indexes can require some tending, just as tables do.
<4> RealRaven: What's the problem?
<2> Xgc: i just want to know if I have holes or not
<4> RealRaven: Drink water. Look for leaks.
<2> Xgc: but I've seen nothing to measure hole-degree or somthing
<2> ohh, the old "there's a hole in the bucket..."
<4> RealRaven: What type of holes?
<2> the ones Jay Pipes always talks about :)
<2> I guess, the ones you get from deleting stuff from a table
<2> and that can be fixed with optimize table
<4> RealRaven: The area freed by the old row data?
<2> yes
<4> RealRaven: MySQL Administrator probably shows some of the detail.
<5> Other than storage requirements, are there any other differences between char and varchar? speed for selects, or anything like that?
<4> RealRaven: Are you trying to determine whether you should rebuild or compress a table or not?
<4> AaronCampbell: Yes.
<2> Xgc: yes
<4> AaronCampbell: The fixed width nature of char can lead to improved performance.
<5> Yes, it's faster? in general, or specific situations?
<2> Xgc: and hopefully speed up deletes of large portions of the table
<5> thanks Xgc
<4> AaronCampbell: You're welcome.
<6> Is there an Adept Repository that installs mySQL 5.x? The default Repositories reference mySQL 4.x.
<7> ah ha!
<7> anyone awake?
<8> Im here
<8> I have a question though
<8> can i do a delete query using 2 tables?
<8> Like DELETE FROM users WHERE users.id = payment.id ?
<8> Like in INSERT clause?
<7> possibly, bit to deep for me
<2> opCoder32xz: 1204
<2> 1205
<2> 1207
<2> 1210
<2> 1213
<2> jesus.. .what did I paste? :)
<2> this was what I was suppose to paste
<2> http://dev.mysql.com/doc/refman/5.0/en/delete.html
<2> you can do delete from several tables
<2> but if you just want to delete from one you have to do a inner select to get the WHERE-clause right
<8> Yeah why did you paste that?
<8> What are those numbers for?
<2> nothing, wrong pastebuffer
<8> BTW no problems thanks, i got it already a while ago :)
<2> sorry
<8> No problem
<9> Can someone help me with this query? http://pastebin.com/745072
<2> I might, Im feeling helpfull :)
<9> awesome
<2> I guess full_sized_closet exists :)
<9> yea
<9> as do those fields
<10> can someone advice me on how to optimize my queries and are there commands to aid with this?
<10> sorry got dc'd
<2> notx: yes, "explain SELECT * from WhateverTable"
<2> notx: make sure you use indexes
<10> thats it?
<9> RealRaven: any idea?
<2> notx: that isn't little, try it
<2> Super-Fly: not yet
<9> k
<2> do you need all thos ' around everyting?
<9> They just mysql its a field
<2> Super-Fly: do you use contrains?
<2> foreign keys?
<9> nope
<2> alright
<2> Super-Fly: I can't see whats wrong, the documentation suggest foreign keys but you don't use that
<9> yea this is pissing me off
<2> try with a smaller index
<2> try to exclude
<2> parts of it, see if one column is the problem
<2> I read somewhere that 14 columns was max, perhaps one yours are big and it grows the index to large
<9> maybe
<9> I'll try with one or 2 I guess
<9> same thing
<5> Super-Fly: it IS a MyIsam table right?
<9> sure?
<5> Super-Fly: just asking because FullText is only available on MyISAM tables
<9> I just altered the table and set it as that, let me try again
<8> Not unique table/alias: <-- What is this error about?
<9> yea, same thing
<5> Super-Fly: http://support.navicat.com/?_a=knowledgebase&_j=questiondetails&_i=67
<5> Try that...starting MySQL with a specific temp dir...making sure it can write to that directory for it's temp tables
<11> Super-Fly try using a newer version of mysql
<11> one that wasn't compiled on a 486
<9> I can't update it
<9> or, at this point, I'm not prepared to update it
<9> mysql says this should work with the version I'm using, so I don't see a reason why I should update it
<2> Super-Fly: do you have a lot of data running in you database
<9> this database that I'm working with? no, not to much
<2> then why not upgradE?
<12> Hi Guys
Return to
#mysql
or
Go to some related
logs:
ntp-simple chroot ubuntu
#centos
ERROR 1033 (HY000): Incorrect information in file
#postfix
connect remote mysql-server4
#perl
andrejkw qemu
#gentoo
#kde
bitrch labs
|
|