#mysql
07 October 2007
Total 21 pages. You are browsing page 1/21.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Sun Oct 07 00:00:57 2007
00:03 <****> , which is a (SELECT ...) AS correlation_name
00:03 <****> The key was hearing that a subquery in a select clause could reference a field from the main query table
00:03 <****> That seems a bit strange for me
00:04 <****> But I'm happy that it seems to work
00:04 <****> bigfleet: Yes, but that's not always a good thing to do.
00:04 <****> bigfleet, that is called a correlated subquery
00:05 <****> Xgc: I can recognize that it's not optimal
00:05 <****> My hope is that having completed the desired functionality
00:05 <****> bigfleet: You might try the non-correlated form. That's often processed more efficiently.
00:05 <****> I can show in real numeric terms the cost of doing things this way
00:05 <****> Xgc: what does that look like?
00:06 <****> Because, from a query point of view, it just gets hairier from here
00:06 <****> bigfleet: It looks like the form using derived table(s).
00:06 <****> I'm dealing with leftovers from other software folks that have relatively little conscience
00:06 <****> Here's where I am so far
00:06 <****> And I know there are two nasty ones left
00:07 <****> bigfleet: If you get to a convenient opint in building the SQL, feel free to post it to a pastebin for comments.
00:07 <****> select members.id, (select COUNT(*) from trades where trades.member_id= members.id) as trade_count, (select COUNT(*) from postings where postings.member_id= members.id and postings.type = 'TradeNote') as trade_note_count, (select COUNT(*) from postings where postings.member_id= members.id and postings.type = 'Blog') as blog_count from members
00:07 <****> Ahh, nice
00:07 <****> Good reminder
00:08 <****> bigfleet: Also, don't forget to use EXPLAIN to look at the execution plan.
00:08 <****> I transfered my databases to a new server.
00:09 <****> I found that the non-ASCII chars asre not correctly interpreted in the new server.
00:09 <****> canm I fix that ?
00:10 <****> nachalibre: If the export is valid, yes.
00:10 <****> the data is there
00:10 <****> but characters like á , é ñ
00:15 <****> nachalibre: If you mean some of the export is valid and some is not, then the export is not valid.
00:17 <****> nachalibre: If it's completely valid and compatible with the version of mysql in which you wish to load it, you'll have no problems.
00:18 <****> nachalibre" Just make sure everyone, who touches the data on the way in, supports the correct character set.
00:21 <****> Hi there ... Quick question which I can't figure out. I want to select from my "logins" database all the users who have not logged in for more than 2 weeks. There are the date / time in the database as "dateadded" -- how should my Select statement look?
00:22 <****> yes
00:22 <****> the point is that
00:23 <****> the source database tables use UTF-8
00:23 <****> the new mysql tables also are using UTF-8
00:23 <****> but instead of "falló" i getting "falló"
00:24 <****> I would like to know if it is just a display problem, or a data corruption
00:24 <****> i mean, the real value is ó or ó ?
00:25 <****> Danny: See date/time functions and consider comparing against NOW() - 2 weeks
00:25 <****> okay
00:25 <****> how can i get mysql 5.0.45 to multithread on Slackware 11
00:26 <****> nachalibre: Find the insert statement and see if the data looks correct, from a terminal/editor that supports that character set.
00:27 <****> lls: it already is
00:27 <****> jbalint, i only see one thread/process
00:27 <****> lls: check again
00:27 <****> jbalint, i did
00:28 <****> jbalint, in top and ps
00:28 <****> jbalint, there is mysqld and mysqld_safe once
00:28 <****> mysqld is one process
00:29 <****> how can i get multiple?
00:29 <****> why not just let it be?
00:29 <****> its fine
00:29 <****> my mysqld is overloaded but the server usage is only 24%
00:29 <****> how many CPUs? how many concurrent queries?
00:29 <****> 2xdual core
00:30 <****> 40++ concurrent queries
00:30 <****> how much cpu is spent waiting for io?
00:31 <****> not much
00:31 <****> most of the tables are memory tables
00:31 <****> are you performing alot of updates? with table locking?
00:31 <****> jbalint, and the mysqld process in top has %CPU 200
00:32 <****> thats already more than 24% of the whole box....
00:32 <****> where did you get 24% from
00:32 <****> top
00:35 <****> jbalint, http://paste.ubuntu-nl.org/39808/
00:37 <****> seem fishy, 181/4 should be <= 100-64.9
00:37 <****> yes ENGINE=InnoDB DEFAULT CHARSET=utf8
00:38 <****> the load is high too
00:38 <****> Xgc gnari: Final query that produces correct results-- http://pastie.caboo.se/104541
00:39 <****> Sorry about the lack of whitespace, is there somewhere better?
00:39 <****> jbalint, anything i can do about it?
00:39 <****> do you have vmstat or iostat
00:39 <****> vmstat
00:40 <****> and iostat
00:42 <****> jbalint, i have both installed
00:42 <****> well run them, and see where the io is concentrated
00:43 <****> and you are sure you have over 40 concurrent requests?
00:44 <****> jbalint, yes
00:46 <****> jbalint, there seems to be very little IO usage
00:51 <****> Have a problem, I stopped a restore in the middle, disabled slow-query-log, and now mysqld wont start.
00:52 <****> just one process of mysqld in ps and not others, and it says cant connect to socket.
00:53 <****> if I skip-innodb is starts...
00:54 <****> Anyone have an idea?
01:17 <****> this is my first time on freenode.net ... It appears efnet no longer has people who want to help much any more..
01:17 <****> sql /join #mysqlhelp
01:17 <****> opps
01:20 <****> rdm2: You think this place is better????? :)
01:20 <****> hah... hard to say
01:20 <****> more members. =)
01:21 <****> Those are just my clones
01:21 <****> is this the right channel to ask sql syntax ?
01:21 <****> It's just me and you today
01:21 <****> A MySQL client is a better place to ask about syntax... it's pretty smart
01:21 <****> It even tells you if you gots some errs
01:22 <****> .... Yes, you may ask :)
01:22 <****> I think I actually just figured it out
01:23 <****> Woohoo! See, we are helpful
01:23 <****> magical
01:23 <****> here's my statement, it's working, but is there a better way to do it?
Total 21 pages. You are browsing page 1/21.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last