…we’ve been quietly developing a MySQL protocol parser for Maatkit. …1s # 10s+ # Tables # SHOW TABLE STATUS LIKE ‘dual’\G # SHOW CREATE TABLE `dual`\G # EXPLAIN select 1 from ( select sleep(.1) from…top of the report — what is up with the 12% of queries that say No_index_used? If we increase –limit a…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… refreshed. This is not much different from building a table with CREATE TABLE .. AS SELECT. In fact, this is actually part…MV. mysql> select mview$pk as rank, customer_id, total_price, total_lines from demo.dashboard_top_customers limit 10; +——+————-+————-+————-+ | rank |…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…a cold MySQL instance, then it would require reading nearly 40 pages from…10s+ # Tables # SHOW TABLE STATUS FROM `wp_blog_one ` LIKE ‘wp_options’\G # SHOW CREATE TABLE `wp_blog_one `.`wp_options`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
…MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows, or rather return top 10 rows from sorted result set which is the most typical way sorting is used in practice. I tested full table…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…SELECT id FROM clients)” which is a…top 10 most expensive queries, in total execution time. By the way, the slow query logs in the stock MySQL…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…tables (selects in FROM clause) are used. MySQL will execute these subselects on explain to provide plan for top…from other tables. No information about using disk for sort or temporary tables MySQL…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
…top…TABLE `comments` ( `user_id` int(10) unsigned NOT NULL, `comment_id` int(10…mysql> explain select * from user_counts where user_id=5 \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…top ads by clicks, top…MySQL conferences from people who have built systems like yours, and there’s a…tables, it lets you scan portions of a table instead of the whole table if you a…tables is hard work for the database server. If you do it on the master with INSERT..SELECT…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 16 Comments
…count(*)) on a range of records. This table is partitioned by month which means that MySQL can use …;text-indent:0px;padding-left:3px;border-top:1px solid #CCC;border-right:1px solid #…ORDER BY c DESC LIMIT 10; Q4 – SELECT carrier, count(*) FROM ontime WHERE DepDelay>10 AND FlightDate between ’2007-01…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
…top sales people. This information may often need simple select…mysql> explain select grp, count(*) cnt from dt where slack like “a%” group by grp \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table…
