Wadle: I have a typical scenario like Posts HAS MANY Comments where I want to get number of comments for each post. When my posts table is 800K rows and comments 3 million rows. its taking about 7 seconds to run. I also need to sort by count so I guess that means it has to scan both tables entirely. Anything I can do here to speed it up?
Felske: Nsanden: materialize counts
Rogado: You can also index counts then
Vidler: I dont follow. Materialize counts?
Wernsman: Save the count in the post table?
Pastorin: Routhier: interesting, thanks
Rieves: What if I want the number of comments in for each post but only in the last month, or last week?
Hyten: Store that too in post table?
Obie: Nsanden: can do some math!
Seyller: Http://research.microsoft.com/pubs/77611/quantiles.pdf
Goergen: Moving average math can also work
Jobson: Or materialize individual days/weeks and sum then up, although you will lose ordering capabilities then
Agers: Yeah im thinking that math is a bit out of my league
Stratz: Domas: very nice. thanks for the link. :-
Davis: It is always fun to respond to questions in full seriousness!
Maxin: Hi Mysql Folks; does locking in innodb only work in transaction or can i use it anywhere?
Naslund: Everything is a transaction
Troxell: That’s what i thought
Brent: Bish: avoid explict locking. know what transactions are and use them correctly.
Steeby: Hi. I have an HA setup between 2 masters and 1 slave. Our health check logs into db2 from db1, and runs show slave statusG;. Is there a faster way to get this info? Sometimes running this on a remote node takes up to 20-30 seconds.
Naslund: There are some interesting cases where it hangs
Naslund: 5.7 has non blocking show slave status
Leo: Deviantlinux: https://www.percona.com/doc/percona-server/5.6/reliability/show_slave_status_nolock.html here’s a nonblocking option that may help
Naslund: I guess percona 5.6 has that too :
Naslund: It hangs when stop slave is waiting on a query to finish
Reyelts: Danblack, yeah i will do but first. update for sharing is fine with me ; i only have a few updates in my code
Nimox: I didn’t even know about that
Luthe: Danblack, what is implicit locking :o?
Walczak: Do transaction magicially save my code from ****ing up each other?
Birchett: I mean when i use the start transaction stuff
Korenic: Implicit locking is letting innodb process your transactions with minimal effort.
Marolt: Group related transaction in an atomic block so either they succeed or fail together.
Waddick: Bish: it also means when your code tries to update the same data in parallel one will fail. make sure you handle errors
Hyatte: And why is explicit locking bad? mean cannot wait one wait for the other?
Zwickl: Its a bad patten to try to explictly locks things. It shows a lack of understanding/trust in transactions to do their job. select for update is ok.
Propheter: Https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlocks.html is the link i was looking for
Gillitzer: Regarding error handling
Wix: Im using for update, can i use it on the updates only, too?
Helmsing: Bish: UPDATE FOR UPDATE ?
Vandeman: Oehm lemme look what the library im using is doing
Helmsing: Bish: I was not serious.
Tetro: I know, but i actually don’t know what the library i use is doing, i can tell it to use “for share”, and it does select data with “LOCK IN SHARE MODE”
Delhierro: But i don’t know what it does when updating
Kloska: Or if a transaction will be present. or if i have to start end end it by myself
Brantley: And locking a row in mysql means another transaction will fail, not wait for the other one to end :o?