…) -> JOIN demo.order_lines as ol USING (order_id) -> GROUP BY (customer_id) -> ORDER BY total_price desc -> LIMIT 10; +————-+————-+————-+ | customer… I delete some line items from orders: mysql> delete -> from order_lines -> where order_id -> between 1 and 100 -> limit 500; …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
… 0 COUNT_DELETE: 0 SUM_TIMER_DELETE: 0 MIN_TIMER_DELETE: 0 AVG_TIMER_DELETE: 0 MAX_TIMER_DELETE: 0 *************************** 3. …that required read IO mysql> select * from file_summary_by_instance order by COUNT_READ desc limit 6G *************************** 1. row *************************** FILE_…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
…your benefit. This is followed by a description of FlexCDC, …affects every transaction. Finally, MySQL has limited trigger support, some of which… Warnings: 0 And delete data in a second transaction: mysql> delete from test.demo …change (insert is 1, delete -1), the transaction order and the source of…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…mysql> explain (select * from people where age=18 order by last_online desc limit 10) UNION ALL (select * from people where age=19 order by…
Post: MySQL Slow query log in the table
May 31, 2007 By 1 Comment
…MySQL 5.1 get MySQL slow query log logged in mysql.slow_log table instead of the file as … * FROM (SELECT * FROM slow_log ORDER BY start_time DESC LIMIT 100) l ORDER BY start_time which shows last queries … ? I guess it should be blocked as normal DELETE statements are blocked. More than that. I found…
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
…real value by avoiding using those resources. You need an efficient and reliable cache in order to achieve… what you need Both Memcached and the MySQL query cache are limited in size. If you try to… for example and then the duplicate items are deleted. When this happens, decreasing the counts in …
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…6% 260 0.0002 1.00 0.00 DELETE poller_output # 20 0x428A588445FE580B 0.0449 0.6%…by MySQL. The end result might be that you end up limiting the number of results returned by the query, by using a LIMIT clause or by…tag’, ‘post_format’) AND tr.object_id IN (733) ORDER BY t.name ASC\G Let’s again take a…
Post: MySQL 5.6: Improvements in the Nutshell
January 27, 2013 By 16 Comments
… improvements available in MySQL 5.6 which … Multi-Range-Read (MRR) – Faster ORDER BY nidxcol LIMIT N – Persistent Statistics for Innodb …by User, Host etc – - Network IO Instrumentation – - Show Host Cache Contents – - Improved File I/O Instrumentation – Improved EXPLAIN – - Explain for UPDATE/DELETE…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…limited and I had to transform files to quote each field. After that load statement is: mysql -S /tmp/mysql…BETWEEN 2000 AND 2008 GROUP BY Origin ORDER BY c DESC LIMIT 10 [ "ORD", 739286 ] [ "ATL", 736736 ] [ “DFW”, …that MonetDB supports all INSERT / UPDATE / DELETE statements (and space is price for…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
…test_t_del` AFTER DELETE ON `test`.`t` FOR EACH ROW DELETE IGNORE FROM `test`.`_t_…= ? AND `guest_country` = ? AND `score` >= ?)) ORDER BY `guest_language`, `guest_country`, `score` LIMIT ?, 2 /*next chunk boundary*/ … in the tool, problems with the MySQL server (privileges, etc.), or other oddities, …
