… fetching rows, thread declared inside InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(s), heap size 440760, 799203 row…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… be picked more frequently. Nearly every solution for randomly choosing rows involves some compromise, either of performance, or of accuracy of…. Then you could choose a random value, look up the row with “=” instead of “>” and be guaranteed to find exactly one… TV show. Couldn’t you have used a SUBQUERY to fetch and compare MAX(episode_id) as an option there? Yes…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
… “”; while ($row = mysql_fetch_assoc($result)) { if ($row['Count'] > 0) { echo “” . $row['label'] . “”; display_menu($row['id'], $level + 1); echo “”; } elseif ($row['Count']==0) { echo “” . $row['label...
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… it from the server, fetching in blocks. There are some limits as you can’t use mysql_num_rows() and mysql_data…_fetch_row simply reads data from memory and so most applications do not care to check if there are any errors while fetching. With mysql_unbuffered_query data comes in portions so you can well get an error while fetching rows. If you…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…, ACTIVE 5 sec, process no 3396, OS thread id 1150142816 fetching rows, thread declared inside InnoDB 166 mysql tables in use 1…, ACTIVE 7 sec, process no 3396, OS thread id 1147980128 fetching rows, thread declared inside InnoDB 114 mysql tables in use 1… which is basically what transaction is doing it can be “fetching rows“, “updating” and couple of other values. “Thread declared inside InnoDB…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… reading them protects from this effect as other transaction modifies rows before INSERT … SELECT had chance to access it it will…, ACTIVE 37 sec, process no 29895, OS thread id 4058032 fetching rows, thread declared inside InnoDB 3 mysql tables in use 1… lock structs, which means it has locked a lot of rows. “fetching rows” of course means it is still going. In this case…
Comment: Handling big result sets
June 27, 2006 By 13 Comments
… is being fetched. Also other drawbacks of mysql_use_result: When using mysql_use_result(), you must execute mysql_fetch_row() until a NULL value is returned, otherwise, the unfetched rows are returned as part of the… not use mysql_data_seek(), mysql_row_seek(), mysql_row_tell(), mysql_num_rows(), or mysql_affected_rows() with a result returned from mysql…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
…, locked 2 8316 lock struct(s), heap size 1948088, 10008317 row lock(s) MySQL thread id 53173, OS thread handle 0x7ff75963b700…, ACTIVE 3 sec fetching rows mysql tables in use 1, locked 1 9417 lock struct(s), heap size 915896, 696679 row lock(s… mode, which will not conflict unless they touch the same rows. Now finally lets illustrate how MySQL and Innodb level locks…
Post: What exactly is read_rnd_buffer_size
July 24, 2007 By 4 Comments
… value it needs to be accessed in pretty much random row pointer (typically physical) order. MySQL takes bunch of pointers from…_rnd_buffer_size is important (optimization works in following conditions): Row pointers are stored in the sort_buffer, not the whole… of rows are retrieved after sort – if you have LIMIT 10 it is unlikely to help as MySQL will stop fetching rows by…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…: mysql> EXPLAIN SELECT * FROM employees ORDER BY first_name\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees type: ALL possible… reads, no problem) and then lots of random reads to fetch rows by index value. And random reads are orders of magnitude…
