Cordes: Harrison-n: never heard of indexing
Atengco: Termitator sends my input to all tabs
Stodghill: Yeah even tabs that aren’t in a visible panel 😛
Wilhelm: Quick question: select foo, select foo1 from bar1 where wtvr from bar; OR select foo, foo1 from bar join bar1; ?
Flieller: Hello! a long-running several hours query over the network stops suddenly with the message ‘killed’. Is there a way to debug this without access to the mysql log?
France: Most “subquery vs. join” information out there addresses subqueries in the FROM clause. Is it the same for the SELECT clause?
Decoux: Regedit: a correlated subquery in the SELECT clause is just as bad.
Stodghill: Luto: Someone killed it
Nemer: Luto: there is something terribly wrong about a query that runs for several hours
Decoux: Regedit: if the query is not correlated, it’ll only be executed once.
Stall: Decoux: well it does depend on a per-outer-query-row value, is that what you mean by correlated?
Suchla: Regedit: well, I’ve got reveral 10s of million of rows and can’t create indexes please don’t ask.
Vazzana: Stodghill: I read about max_statement_time. Does this produce a different output?
Newhouse: Luto: is this a shared hosting account
Mimaki: Regedit: nope, but the guy with access to the log is sleeping :
Off: Got a block size question here. I have a few devices with 16k native block size, what options do I have for using raw devices?
Stodghill: Luto: Are you using MySQL 5.7?
Dyckman: If I raid them together then the stripe size will be a large multiple of 16k
Stodghill: Then you don’t have max_statement_time
Grzywinski: Which is probably not ideal
Painter: So there is pretty much only the option of somebody or a script killing it?
Vanderkar: Unless mdadm is really smart about that sort of thing
Dziuba: Decoux: do you happen to also have any words of wisdom / recommendation about the subquery vs. join issue?
Saden: Okay, I’ll ask around tomorrow. Thank you!
Decoux: Regedit: non-correlated subqueries are only run once, and don’t need joins. Correlated subqueries must be converted to a join.
Decoux: Regedit: you can also use a CROSS JOIN to add a non-correlated value to every row instead of a subquery.
Ackert: Decoux: wait so it never makes sense to do a subquery then? cuz it’s always a join anyway?
Warde: Regedit: why is a long-running query horribly wrong per se? I guess batching it would be smart in such a case, but apart from that.
Decoux: Regedit: it rarely does – 5.6 is a bit better at them, however.
Decoux: Regedit: in most cases, a join will outperform a subquery.
Glogowski: Luto: you have all manner of wrong going on there, including but not limited to not having indexes, and leaving the machine in the hands of a human that requires sleep
Stodghill: Luto: Long running transactions cause issues in MVCC environments. In particular it means that no rows can be purged that are newer than when the transaction started, so the undo log gets bloated.
Lacrone: When i do ‘Select for update’ only FROM table rows will locked?
Hansberry: Or joined rows will locked too?
Decoux: Geter: depends on the statement.
Stodghill: And transaction isolation level
Dargenio: I dont see such rules in docs =
Decoux: Geter: SELECT FOR UPDATE will issue a lock on the affected rows, and even surrounding rows.
Decoux: Geter: what rows are updated depends on what table is updated, and the WHERE clause.
Stodghill: Https://dev.mysql.com/doc/refman/5.6/en/select.html — “If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction”
Stodghill: BLZbubba: Why do you want to use raw devices? You can access tablespaces with O_DIRECT by setting innodb_flush_method = O_DIRECT.