| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Comments:
<0> try again
<0> using same host and port
<0> and user/p***
<1> phpMA says no.... so... ??
<1> Ask them?
<2> Anyone care to tell me why the second of these two queries is consistently faster than the first? http://munger.pastebin.com/765573
<1> loef_, Looks like one query would be ok.
<2> They both do the same thing
<3> I3ooI3oo: Using where; Using temporary; Using filesort :(
<3> er will
<3> will[werk]: could i optimize the query somehow?
<1> Munger, Do you always run the second one after the first?
<1> !m loef_ explain
<4> loef_: (EXPLAIN Syntax (Get Information About a SELECT)) : http://dev.mysql.com/doc/mysql/en/EXPLAIN.html
<2> will[werk], No
<2> will[werk], I thought about cacheing
<5> Munger: There is no key on the last row of the first explain plan. Full table scan == ouch
<2> will[werk], I can run them both 20 times each, and the second is always much faster
<5> Munger: On the second one, the second to last is hitting 5K rows, but it's using a where clause on ot_gid as the index
<2> inviso, As far as I can see, the explain plan looks worse for the second one
<5> Munger: How so?
<1> Munger, Are you seeing how fast EXPLAIN runs the query?
<2> OIC what you mean
<2> | 1 | SIMPLE | med | ALL | ot_uid,ot_gid | NULL | NULL | NULL | 7446 | Using where |
<2> That line
<5> yep, that one hurts bad
<2> How can I improve that?
<2> Obviously, I'd prefer not to do the union, because variations on this query will be used extensively throughout my app
<2> I'd prefer a tidier query
<5> Munger: do you have an index on mem.ot_gid and med.ot_gid?
<2> inviso, Yes to med.ot.gid, and an index on mem(ot_uid,ot_gid)
<2> Maybe do away with the compund index and have separate ones?
<5> mem(ot_uid,ot_gid) is not sufficient for that query
<2> So I should split it into two indexes?
<5> Just add another on ot_gid
<2> phpMyAdmin doesn't like that
<5> shoot it
<5> burn it
<5> poop on it
<2> lol. done
<5> my work here is done
<2> I'm not sure that having a compound index serves any useful purpose anyway
<1> lol
<2> in this case
<5> compound indexes are very very useful
<1> Usually "it doesn't hurt"
<5> But, in a compound index like above, note that it's sorted first by uid then by gid. Thus, it can't be used with gid is the "only" criteria. If both criteria were included, it could narrow the rows down very effeciently
<5> *when
<2> the ot_membership table (mem) is simply there to provide a many-to-many relationship
<2> so two compound indexes, one the reverse of the other?
<2> or is that pointles?
<5> Most likely not necessary. Just another single index on gid is probably enough
<5> It really depends on the usage though
<2> I don't think I will ever search this table on both keys
<5> And if it can even hit one column through an index, that reduces the search space significantly
<2> Like i said, it's just there to proveide many-to-many
<5> Add a second, single column index on gid and take a look at it again. You should see some very nice gains
<2> I never ask "Is this user a member of this group?" I do however ask "Which groups is this user a member of?" or "Which users are a member of this group?"
<2> So two single indexes should be OK
<6> Is this user a member of this group and Which users are a member of this group are quite similar
<2> EyeCue, Similar, but different
<6> depends how you structure it.
<6> if you have a group_to_user_relational, then not really.
<6> select gid from group_to_user where uid=x, vs select uid from group_to_user where gid=y
<6> then again, i joined the convo late, who knows if im being relevant or not :)
<2> inviso, I added the index, but it still isn't using it. It's faster, but still slower than the other query
<2> inviso, http://munger.pastebin.com/765630
<2> 182 rows in set (0.08 sec)
<7> Using two columns in GROUP BY doesn't behave as expected.
<7> I still get many results which have the same value for the second column
<7> GROUP BY col1, col2
<7> It does work for col1, but not for col2
<8> EricCartman_: Depends on what do you expect to happen
<7> I'm expecting what happened with the col1 to happen with col2
<8> EricCartman_: It works exactly as ANSI standard dictates, but that indeed can differ from your expectation
<7> col2 has same value for a few records
<8> EricCartman_: So what?
<7> col1 does not
<7> oh btw, they refer to the same table with different aliases
<9> no kitty, that's my potpie!!!
<7> I guess that could be the problem
<10> Mooommm, kitty's being a dildo!
<7> As in `GROUP BY t1.col, t2.col`
<9> xDD
<8> EricCartman_: So?
<7> salle: you are very welcome to shut the **** up if you are not willing to help. You annoy me.
<8> EricCartman_: By doing this you instruct the server to aggregate the distinct combinations of both columns
<11> how can I update a md5 p***word? Admin | Emanuel | Administrator | c4ca4238a0b923820dcc509a6f75849b | | 900 | en_gb | 30 (something with update.. I forgot)
<8> EricCartman_: What is your problem? You said it doesn't work as you expect, but you never said what exactly do you expect
<12> give em hell cartman
<1> EricCartman_, Calm down.
<1> salle, You're going to put up with that?
<8> EricCartman_: For table like:
<8> +------+------+
<8> | col1 | col2 |
<8> +------+------+
<8> | 1 | 1 |
<8> | 1 | 2 |
<8> +------+------+
<8> EricCartman_: And query like:
<8> SELECT col1, col2, COUNT(*) FROM gr GROUP BY col1, col2;
<8> +------+------+----------+
<8> | col1 | col2 | COUNT(*) |
<8> +------+------+----------+
<8> | 1 | 1 | 2 |
<8> | 1 | 2 | 1 |
<8> +------+------+----------+
<9> xD
<8> EricCartman_: Which is exactly how it should work
<12> I actually won money at the kentucky derby
<9> nice
<12> $200 on barbaro to win baby
<12> glad I didn't bet on him again lol
<13> hello all
<9> lol
<9> poor horsie :(
<13> how can I do a full text boolean search with just negated search words ex: -exclude
<1> salle, You're too nice :)
<12> yeah =( he was really a great horse, he pwnt the derby pretty hard (looked to me like the jockey was just ****ing around till the home stretch, then he put the spurs to him)
<12> salle let's run away together; me, you, mysql, boca raton
<8> will[werk]: ?
<8> will[werk]: Nice no
<8> will[werk]: I'm just having fun here
<12> halorgium, email me at erudified@gmail.com with your resume
<12> and I'll get back to you by the end of this week
<12> ergh sry
<8> EricCartman_: Say something please :)
<9> I'm looking for work in Barcelona, but damn Spaniards don't pay squat :\
<9> i'm used to my comfy salary here in L.A., I just hate this place
<8> EricCartman_: Tell us what you expected at least and what do you want to do so we can tell you how can you do it
<14> can anyone help me? i have a question
<1> !tell us about ask
<4> will[werk] asked me to tell you this: Don't ask if you can ask a question or ask if anyone knows about a particular topic. Just ask your specific question, then wait to see if someone will help.
<8> erudified: What is boca raton?
<1> EricCartman_, Is salle on your ignore list?
<15> hey folks
<8> hehe
<7> SELECT t2.* FROM membersTree AS t1 JOIN membersTree AS t2 ON t2.id = t1.parent JOIN members ON members.id = t2.memberId GROUP BY t1.parent HAVING COUNT( t1.parent ) > 9;
<15> just installed mysql on server...
<7> This is my query. t2.parent returns many records with same value.
<14> i set up mysql 5.0 Im having a problem setting up an administrator account, on my sql like i dont know if i did it wrong or what but it wont let me connect to the mysql database
<1> Time to learn how to use GROUP BY...
<7> I want GROUP BY on that too, as I've did with t1.parent
<8> EricCartman_: Which is correct .....
<15> server is up, but my client app (mysql administrator) is "not allowed to connect"
<7> But it does NOT work.
<15> do I need to somehow enable external clients to access mysql?
Return to
#mysql
or
Go to some related
logs:
patch dapper madwifi injection
fedora ibm x206m
ebuild cx_Oracle gentoo
ubuntu omnetpp
yast partition.manager external.drive
difference imps/2 explorerps/2 ps2
wwwhizz
ntldrl xp
caferadius cf install
maximum XvImage size xorg.conf
|
|