… find out queries which should be optimized. By looking at pt-query-digest report it is easy to find slow queries or queries which… to ones which examine one table only. What is about queries which query more than one table ? # Time: 120911 17:25:22… b where a.id=5 and b.id=a.k; mysql> explain select * from sbtest a,sbtest b where a.id=5…
Post: Hidden columns of query_review_history table
August 28, 2012 By Leave a Comment
… can use pt-query-digest to process a MySQL slow query log and store historical values for review trend analysis into query_review_history…’, ‘host’, ‘db’ which are not included by default. I will explain how to implement this. Also the documentation says: Any columns… slow query log from the another server. Having hostname set might be useful for filtering queries in the database aggregating slow queries from…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
…we really need is with 2147483647) mysql> explain select count(*) from trunc where i=4147483647; +—-+————-+——-+——+—————+——+———+——-+——-+————————–+…here Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select count(*)…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
…); Query OK, 0 rows affected (0.11 sec) mysql> create table j3 (c1 int); Query OK, 0 rows affected (0.10 sec) mysql> explain extended…; insert into j3 select * from j2; Query OK, 1 row affected (0.00 sec) mysql> explain extended select j1.c1 from j1, j2… algorithm. For example: mysql> create view v1 as select * from j1; Query OK, 0 rows affected (0.10 sec) mysql> explain extended select * from…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… when MySQL is doing this; run an EXPLAIN on your SELECT and you’ll see “index_merge” as the type of query and… hints are fairly unintrusive and can be applied on a query-by-query basis, so there’s no need to go mucking…=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type) WHERE…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… changing any queries directly. Unfortunately it does not work. It is interesting to see EXPLAIN for such query and time for the query which… as getting only one row, and note even EXPLAIN takes same amount of time: mysql> explain select * from user_counts where user_id… create a very artificial query which will JOIN 2 views just to see how indexes are used: mysql> explain select uc.cnt+uc2…
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
… added for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It… shown by EXTENDED EXPLAIN for some reason, while it would be quite helpful. Finally lets look at third example: mysql> explain extended select…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…); +———————————-+ | name | +———————————-+ | ed4481336eb9adca222fd404fa15658e | | 888ba838661aff00bbbce114a2a22423 | +———————————-+ 2 rows in set (0.00 sec) mysql> explain SELECT sql_no_cache name FROM people WHERE age in… as BETWEEN range or as IN filesort appears and query becomes very slow: mysql> explain select * from people where age=18 order by…
Post: When EXPLAIN can be misleading
November 12, 2006 By 1 Comment
… want to profile your queries if you have any hesitations in EXPLAIN accuracy. The other problem however is EXPLAIN does not take LIMIT… LIMIT is used. Take a look at this simple example: mysql> explain select * from rnd order by r desc limit 1 \G… limit. The other little annoyance is – MySQL will report these queries to slow query log if –log-queries-not-using-indexes is enabled which…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… points you need to attack the right query in the right way. But vanilla MySQL does have its limitations, it reports only… general query log. Installing pt-query-digest tool (as well as other tools from Percona Toolkit) is very easy, and is explained here… the queries that you can use to gather more data about the underlying tables involved and the query execution plan used by MySQL…
