logs archiveChat DB / Freenode / #mysql / 2015 / October / 6 / 1
FrozenFire
Is there any way to influence the selection behaviour of a non-full group by?
Other than aggregation
snoyes
Not really. What are you trying to accomplish?
thumbs
FrozenFire: group by a deterministic column
FrozenFire
In my above-described issue, I think the solution here is to group by the "recoveries" column I have, which should always be the same for multiple records in a series, and then pick the first record within that group.
thumbs
!t FrozenFire groupwise max
ubiquity_bot
FrozenFire: http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.6/en/example-maximum-column-group-row.html
FrozenFire
So I'm trying to figure out how to group by that column and have the first record in each group be selected
Cool. I'll try that
Thanks
thumbs
FrozenFire: use MIN(othercol)
FrozenFire: you may need to use the derived table approach, too.
FrozenFire
Does this work in a view, by the way?
I noticed that some things simply don't.
thumbs
FrozenFire: why are you using a view?
FrozenFire
Lots of reasons that can mostly be summed up as "I can't update the queries in the applications performing the queries"
So I just have to provide a view, which provides the underlying query which I can update
The columns remain the same, but the results change
thumbs
FrozenFire: ok. You can do whatever you want with your view, then.
Renacor
could somebody be kind enough to help me with this query that is taking infinitely long for some user_ids http://paste.ubuntu.com/12692177/ ?
aliske
what are you trying to do?
Renacor
basically that last self join is hanging up the query
not sure why
if I rip out that last left outer join and LastUserLogin.* then it will work fine
some user_ids that dont have many LastUserLogins it works fine
aliske
sounds like you should add a limit on it
Renacor
there is a limit
did you check out the pastebin?
thumbs
Renacor: your aggregation is invalid, to start.
Renacor
elaborate?
thumbs
!t Renacor grouping
ubiquity_bot
Renacor: Most implementations of sql don't allow the mixing of grouped and ungrouped columns in the SELECT clause. mysql does, but the row selected for the ungrouped column is an unpredictable value. The groupwise max solution is a way to specify the value selected for the ungrouped column.
thumbs
Renacor: you should always produce correct results first, then fix the performance issues.
Renacor
the join on the user_logins to user table is whats causing it to hang, if i rip that piece out it works fine =\
I am not sure I understand what grouping is messing it up?
thumbs
Renacor: the range condition in the ON clause may cause performance issues, but that's not your main problem.
Renacor: you need a GROUP BY clause.
Renacor
LEFT JOIN `dev02per_test`.`user_logins` AS `LastUserLogin`
ON ( `User`.`id` = `LastUserLogin`.`user_id` ) < that is causing the hangup, if I remove it it works fine
so I am trying GROUP BY User.id but that doesnt work either
thumbs
Renacor: that's not deterministic.
Renacor: again, fix the broken sql logic before you address performance issues.
Renacor
sorry I didnt write this query, I dont see the broken logic
thumbs: could you tell me which aggregation you are referring to that is wrong?
I dont see one other than the counts
thumbs
Renacor: your group by expression is missing.
Renacor
thumbs: could you give me an example of what it should look like?
thumbs
Renacor: if users.id is deterministic, it may work
Renacor
yeah it doesnt seem to make a difference
it tried group by User.id and group bye LastUserLogin.id
thumbs
Renacor: next, let's see SHOW CREATE TABLE for each table.
Renacor
k
thumbs: http://paste.ubuntu.com/12692404/
thumbs
Renacor: that joins isn't using the index, for some reason
Renacor
thumbs
thumbs: I forgot to mention, not sure if it is relevant, user_logins.user_id used to be a varchar
thumbs: which broke the join obviously, i modified it to be an unsigned int(11) like the User.id
is that somehow related?
thumbs
Renacor: well, joining an INT with a VARCHAR won't use an index, for sure.
Renacor
right
thats why I changed it to be a int11
not sure if there is some statistics or old data that still makes the join messed up because of that
thumbs
Renacor: ANALYZE TABLE could help, or just try to force the index
Renacor
thumbs: yeah i had tried that
how do u force the index?
thumbs
!m Renacor index hints
ubiquity_bot
Renacor: See http://dev.mysql.com/doc/refman/5.6/en/index-hints.html
Renacor
so you are saying the LastUserLogin is not using the index right?
thumbs
Renacor: EXTRA: NULL
!m Renacor using explain
ubiquity_bot
Renacor: See http://dev.mysql.com/doc/refman/5.6/en/using-explain.html
Renacor
yeah i am looking at that as well, was trying to figure out if extra tells u that or if it is really just extra data
wait nm
that would be the company/department tabl no?
thumbs
Renacor: focus on line 36 of the other paste
Renacor
k
fergal
hi guys, is there some kind of visual sql statement builder online? i have a bunch of sql statements that get what i want, but i want to combine them into the one statetement, so i only have to make one db query, rather than 4...
thumbs
fergal: learning sql basics will be far more useful to you than a graphical editor.
mgriffin
also, sometimes running four sane queries and assembling in the app is much faster than the query that tries to get everything at once but ends up using bad patterns
kolbe
fergal: thumbs and mgriffin both make very wise points!
fergal: this magical "visual sql statement builder" is unlikely to exist or be something most folks in here would use, but if you can show what you're really doing, it's possible someone can help with some specific advice for the real situation you're trying to deal with
badcom
Hi guys
Fall
Apologies.
badcom
Is it possible to group by a column and then group by the whole result by two other columns? Does it make sense?
kolbe
badcom: that doesn't make sense so far
badcom: if you could show some sample data and desired result, that might help
badcom
kolbe, I'll try that
kolbe
grrreat
badcom
kolbe, let's see if you get the gist by looking at this code: http://pastie.org/10462803. What I need here is to the the total via the aggregate SUM. The problem here is that t3.shift_id is not an unique field and I can't change that now, therefore the total is being duplicated because of that left join with non unique column. How could I go about that without adding a primary key to T3?
to get the total*
I also need that existing group by
whomp
what does it mean to say that a table is nontransactional?
danblack
whomp: you can't do transactions on them - i.e. isolation levels, grouping updates, rollback.
kolbe
whomp: usually means it's using the MyISAM storage engine. not crash safe, not atomic, etc. you should use innodb instead.
Renacor
thumbs: does key_len maybe have something to do with it
whomp
cool. i was actually wondering because i'm trying to figure out what it means that a google datastore commit is transactional or non-transactional, but there are no active irc channels for that question. would you guys guess that it's safe to run a non-transactional commit consisting of a single instruction?
Renacor
thumbs: all the user_id indexes show key_len of 4 except this one on user_login shows 5
i.e. if they indexes are identical (same keys/data types/size) then the key_len should be the same no?
hmm k so nullable vs non-nullable changes the key length from 5 to 4
could somebody explain to me why this doesnt work? mysql> alter table user_logins drop primary key, add primary key(user_id);
1 2 next »