…gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, … frequently. Nearly every solution for randomly choosing rows involves some compromise, either of performance, or…Couldn’t you have used a SUBQUERY to fetch and compare MAX(episode_id) as an option…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
… KEY (`id`)) —————————————————————————————————— $mysql=mysql_connect(’127.0.0.1′,’root’,”); mysql_select_db(‘test’,$mysql); function display_menu($parent, $level) { $result = mysql_query(“SELECT a… “”; 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'] . “”; } else…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… sec fetching rows, thread declared inside InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(s), heap size 440760, 799203 row lock(s) MySQL thread id…
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… real call you can get errors is when you run mysql_query , mysql_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…
Comment: Handling big result sets
June 27, 2006 By 13 Comments
… the data 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…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
… 9 sec mysql tables in use 2, locked 2 8316 lock struct(s), heap size 1948088, 10008317 row lock(s) MySQL thread id… 3 sec fetching rows mysql tables in use 1, locked 1 9417 lock struct(s), heap size 915896, 696679 row lock(s) MySQL thread id… are it is row level locks or auto increment table level lock (mostly with MySQL 5.0 and older MySQL versions). MySQL level locks…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… in KEY_COLUMN_USAGE, we can write a query to fetch every distinct KEY_COLUMN_USAGE.CONSTRAINT_NAME where the REFERENCED… shows us which rows in Bar are orphans. mysql> INSERT INTO Foo (A,B) VALUES (111,2222), (333,444); mysql> INSERT INTO Bar… any time: $ cat generate_foreign_key_checks.sql | mysql -N | mysql -E *************************** 1. row *************************** test.Bar.ID: 42 DIY RI We can get…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… to provide our estimates. If we’re running Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes, Innodb… ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 (mysqld) S…: 21847267 BYTES_SENT: 336986112 BINLOG_BYTES_WRITTEN: 0 ROWS_FETCHED: 485139 ROWS_UPDATED: 0 TABLE_ROWS_READ: 610954 SELECT_COMMANDS: 181243 UPDATE_COMMANDS: 0…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…_p cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row_search_for_mysql,ha_innobase::index_read,handler::read…_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row_search_for_mysql,ha_innobase::general_fetch,handler::read _multi_range_next,QUICK_RANGE_SELECT::get_next,rr_quick,sub_select,do_select,JOIN::exec,mysql_select…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… to the amazing MySQL manual, especially the option and variable reference table…row_lock_time_maxblogpercona.commanual Innodb_row_lock_waitsblogpercona.commanual Innodb_rows_deletedblogpercona.commanual Innodb_rows_insertedblogpercona.commanual Innodb_rows_readblogpercona.commanual Innodb_rows…
