And locking a row in mysql.

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://

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: 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:// 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?