| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Comments:
<0> and hit
<1> since I died in a tragic hovercraft accident involving eels.
<0> Oo
<2> what would be a better/faster way to perform this sql query ... select * FROM artist WHERE name in (select concat("The", name) FROM artist);
<3> So I have a mysql database that stores html formatted text. I want to search this text. Can anyone suggest a way to make mysql ignore the html tags when searching?
<3> follow you're trying to find all artist entries 'Foo' where there exists an artist entry 'The Foo'?
<2> yea
<3> select a1.* FROM artist a1, artist a2 WHERE CONCAT('The',' ',a1.name) = a2.name;
<3> probably be faster than a subselect;
<2> wow, a bajillion times faster
<2> thanks
<4> Hey all, I'm back. I've been playing around with my fresh new MySql install, and in the process of trying to secure the anon p***words, I enter the command from the manual, and mysql returns 'can't find any matching row in the user table.'. Is this a debianism, or did I once again miss something?
<4> I''m ***uming that the command in the manual is to be entered literally i.e. ''@'localhost' rather than putting anonymous in the empty quotes.
<4> Hrm, prolly should have tried that before I came here to bother you.. lemme try.
<5> i am having an issue grasping ON DELETE CASCADE. I have two tables, parent and child. Child references a row in parent. Without ON DELETE CASCADE I i cannot delete the row from table "parent". If I use ON DELETE CASCADE I can delete the row from the table "parent", but the row in the "child" is not removed.
<5> Is that all ON DELETE CASCADE does ?
<6> no, it should delete the row in "child"
<5> gleam_: ah ok... i think i had user error, I just reran tests, and it worked that way . =|
<5> thx gleam_
<6> gotcha
<6> bbl
<7> is it normal that query like this: select * from traf where aff=111 and date='2006-05-23' limit 10000; from table 2.3GB with proper indexes on aff and date takes 40 seconds? the server is dual xeon with 4gb ram, MAX config for mysql and not loaded
<7> mysql 4.1
<3> hmm no d22 with indexes it should take less than a second.
<3> unless you have something like 6,000 columns
<7> Silowyi I use explain? and it says that indexes are in use
<7> no, like 30 columns
<7> it doesnt matter if I do select * or select x,y
<7> http://hashmysql.org/paste/viewentry.php?id=1892
<7> look
<3> do a SHOW INDEX FROM traf;
<3> nvm
<3> can't explain that
<7> anyone else?
<7> Therion?:)
<8> d22: Any change with LIMIT 1 instead?
<5> is aff and date indexed ?
<8> "with proper indexes on aff and date"
<5> d22, you should run the select with EXPLAIN on
<7> moment
<7> http://hashmysql.org/paste/viewentry.php?id=1893
<7> one moment, I will run all these comamnds and paste to you
<5> I love on delete cascade now =)
<7> limit 1 also takes long
<8> d22: date is probably getting you. Only idx_aff is being used.
<7> Xgc how do I fix it?
<7> let me see withotu date...
<8> d22: Composite index?
<7> Xgc what is it?
<7> I dont know if he cashed it or what, but first query with limit 1 took 40 seconds, every next query takes 1 second, evern with different "aff"
<7> lol
<3> maybe the index needed rebuliding
<3> rebuilding*
<9> those index pages will be in cache after the first query
<7> http://hashmysql.org/paste/viewentry.php?id=1894
<7> look on this
<5> d22: i have seen that behavior as well, where first takes longer then subsequenmt queriesw
<7> first it used index of "aff", later it used index on date
<7> can you seer it? why is that?
<10> hey there, is there a fast way to update to the latest version of a beta via mysqladmin?
<7> why is he not using the index on aff and date together? but only on one of them?
<10> I'm guessing no one knows?
<5> i do not know
<10> Okay, I'm running 5.1.7 beta and 5.1.9 is out. Wanted to know if there was a fast way to upgrade.
<5> Idioteque: I would just do apt-get update mysql or up2date mysql depending on if you are on rpm-based distro or debian based
<5> or yum install mysql
<10> I'm on OS X.
<5> Idioteque: eh. wait for Apple to officially released it? =)
<10> Heh
<5> or just down tar ball, and build/install it
<10> I guess I could just manually download the beta file and install from there.
<5> my guess is that it's not a production box
<5> since it's OSX
<10> Nope, development.
<5> yeah, just do it manually, or download fink and see if it's in fink repositories
<5> but if it is super new, it probably won't be
<10> Ahh nevermind. Looks like OS X is still only able to have 5.1.7
<10> Yeah, released today actually.
<10> I *think*
<10> Cool well thanks for the insight anyway, I'm new to all this. :)
<7> do you know a way to measure mysql performacne without making test queries?
<11> d22: show status may give you some information, but it is only some estimates of the current performance for the existing workload
<11> d22: provided it is a running server with load already
<11> d22: for a new installation with no workload, can try the mysql benchmark suite
<12> hi there
<12> is there a way to make a sp or function to return a certain table with more that 1 row and do something like SELECT * FROM (myfunc(2)) ?
<13> Why not make it into a view?
<12> inviso: i tougth of that. I want a func or sp to return a tmp table with the numbers 1 to 10 if I p*** a 'D', 1 to 28/31 if I p*** a 'M' and the month
<13> ewww, what are you actually trying to do?
<12> inviso: return a 1 to 10 or 1 to 31
<12> so that its easier to make a LEFT JOIN
<12> something like SELECT ... FROM xx LEFT JOIN (myfunc('D'))...
<13> If that's all the detail you want to provide, make a couple tables and be done with it
<12> inviso: how can I make a sp return a tmp table?
<14> hi
<14> what's the difference between using "in (subquery)" and using "and" in the where clause
<13> Epilog: why? Make some real tables and don't worry about the SP. They'll be cached as well
<15> much the same as the difference between "or" and "and"
<8> floppyears: One's an apple and the other isn't.
<13> hehe
<14> Xgc: that makes no sense :)
<15> mmmm apple
<8> floppyears: Exactly.
<15> hungry now :(
<12> inviso: how do I change the data? different views for each type?
<14> Xgc: query: http://pastebin.com/736254
<14> that's the query that I was talking about
<8> floppyears: They're equivalent logically.
<8> floppyears: Most engines will likely to treat both as JOINs internally.
<14> so when are subqueries most useful?
<8> floppyears: Strange question. Answer: When appropriate.
<8> floppyears: You can think of the FROM clause as using a list of tables or derived tables.
<8> floppyears: SELECT ... FROM (derived table) t1 JOIN table2 t2 ON ... WHERE ...;
<8> floppyears: The subquery you see is only there because in this case it's called for / appropriate.
<14> thanks Xgc
<8> floppyears: Just keep in mind that most queries / expressions can be written in many different forms. WHERE ... IN (SELECT ...) ... can often be re-written as FROM t1 JOIN (SELECT ...) v1 ON ...; without any change in logical meaning.
<14> thanks Xgc
<14> ok, what's the difference between "not in" and "not exists"
<8> floppyears: You would pick one over the other due to personal preference, readability, clarity, knowledge of some db implementation detail that causes one to be treated better or worse than the other.
<14> thanks again Xgc
<8> floppyears: They're very similar.
<11> would not exist performs better
<11> it can stop once it found 1 row
<12> how do I get how many days a month has?
<8> Sure, but it can't return the negative unless the search is completed, much like EXISTS would have to complete fully if no matches were found.
<8> Epilog: I suspect there's a mechanism for finding the last day of a month.
<12> DAY(last_day(NOW()))
<12> maybe it could had a func to return the days and p***ing the month number
<8> Epilog: CREATE OR REPLACE FUNCTION ...; -- You can do it yourself. :)
<12> Xgc: already doing it
<12> :D
<16> I have two tables that share a common column ("tid"). I want to select the last "rid" (a sub id under "tid") to sort by the last timestamps, and then sort the other table by the newly sorted tids. I have no idea how to go about doing this, and it sounds like the idea can be done an easier way, but I haven't found anything with Google.
<17> hi. after downloading latest mysql beta, ./configuring (with no parameters), installing, and running mysql_install_db, how do I start it properly? running mysqld_safe just stops instantly..
<17> what did I miss?
<8> spencer: If those tids are unique, the second sort will have no impact. In any case, a simple JOIN and an ORDER BY col1, col2; is all you need.
<16> Ah, thank you Xgc.
<8> spencer: Use ASC / DESC to adjust the sort order as you wish.
<18> any recommendations on how to optimize a statement with an OR in it so mysql will use an index?
<19> in postgres you can create a field like XYZ Integer[] Default{'0'} How would i translate that to MySQL ?
<18> xyz INT DEFAULT 0
<18> though the default behaves differently with MyISAM and InnoDB tables... I forget which one, but only one of them actually sets the default properly if you allow null fields.
<19> but doesn't the [] mean something in postgres?
<18> StupidMop: I just ***umed it was the size of the integer
<19> hmmmm...
<19> maybe it means an enumeration or array or something....
<19> i dunno...
<19> yes. it seams [] means an array type in postgresql
<19> does mysql have an array type?
<18> nope
<20> what is the MySQL equivalent of PostgreSQL's "CREATE UNIQUE INDEX username_users_index ON users (LOWER(username));" ?
Return to
#mysql
or
Go to some related
logs:
#oe
#perl
gmail ohgood
#php
+madwifi +Failed dependencies +suse 10.1
boot CD from grub
presentation for A_4
sound-juicer access denied
I want to clear mail queue in Postfix.
serial xubuntu mouse xorg.conf
|
|