… NUM desc limit 5 Follow was index。 the explain: type:index，Extra:Using index; Using temporary; Using filesort i am chinese, so my english…
Post: Repair MySQL 5.6 GTID replication by injecting empty transactions
March 26, 2013 By 1 Comment
In a previous post I explained how to repair MySQL 5.6 GTID replication using two different methods. I didn’t mention… simple reason, it doesn’t work anymore if you are using MySQL GTID. Then the question is: Is there any easy… the slides from that session. I hope you find it useful: MySQL 5.6 GTID in a nutshell
Comment: SimCity outages, traffic control and Thread Pool for MySQL
March 21, 2013 By 16 Comments
… system for the application level via a lightweight python app (using multi threading and internal queues) that accepted connections from the… how to build a private cloud service that can be used to host rapidly scalable NDB clusters (commence with inserting a… finish the articles explaining how it all works) As an aside, setting the pager to ‘less’ is a useful bit when running…
Post: The write cache: Swap insanity tome III
April 19, 2013 By 5 Comments
… Cole explained this here and here. In summary, you need to interleave the allocation of memory for the MySQL process using the….dirty_bytes”, but keep in mind that only one is used; setting one to a non-zero value sets the other…
Post: When EXPLAIN can be misleading
November 12, 2006 By 1 Comment
… see with people using EXPLAIN is trusting it too much, ie assuming if number of rows is reported by EXPLAIN is large query… much faster in case LIMIT is used. Take a look at this simple example: mysql> explain select * from rnd order by r… ref: NULL rows: 49280 Extra: Using index 1 row in set (0.00 sec) This EXPLAIN estimates there would be almost 50…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… you suspect EXPLAIN is lieing you you can use SHOW STATUS “Handler” statistics to see if number of operations match. EXPLAIN works for… not used in optimization which may produce suboptimal plans if stored functions are used in queries. In general in my opinion EXPLAIN needs serious overhaul so it can be used with GUI tool…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… with the MySQL EXPLAIN command, fewer people are familiar with “extended explain” which was added in MySQL 4.1 EXPLAIN EXTENDED can show… the query that I explained used the comma syntax, but when MySQL rewrote the query it switched it to use the JOIN keyword… same. Last, EXPLAIN EXTENDED can show you information about the query rewrites that MySQL makes when accessing views which use the MERGE…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
…: 1 ref: const rows: 185440 Extra: Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count(*) from test_estimate where… ref: NULL rows: 339184 Extra: Using where; Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count(*) from test_estimate where…
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
… the query. This transformation however is not shown by EXTENDED EXPLAIN for some reason, while it would be quite helpful. Finally lets look at third example: mysql> explain extended select * from sbtest where id in (select id from… use IN (1,2,3,…10) it completes in tiny fraction of the second. Anyway EXPLAIN EXTENDED is very valuable addition to EXPLAIN…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
… sec) We can use EXPLAIN EXTENDED to see that MySQL rewrites the view query to include the restriction: mysql> explain extended select c1… old one. We no longer examine millions of rows: mysql> explain select * from v2 where c1 = 10; +—-+————-+————+——–+—————+———+———+——-+——+————————–+ | id | select_type | table…
