… constants) and so ORDER BY can be optimized even if it is done by second table: mysql> explain select test.i from test… JOIN MySQL still will not be able to use it as Optimizer is not smart enough yet to detect such cases: mysql> explain… should write another article about ORDER BY without limit and large tables soon.
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… the values from MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query: mysql [localhost] {msandbox} (test… have been when you have large number of tables or if you do many create/drop of innodb tables. optimizer_switch is the catch… sense in my opinion. MySQL 5.6 adds a lot more optimizer switches which you can play with: mysql [localhost] {msandbox} (test) > select…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this means… made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch=’batched… one number in MariaDB 5.5 that is quite large as compared to MySQL 5.6 and that is ‘Handler_mrr_init…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… following query executed on the InnoDB table: SELECT non_key_column FROM …tuples. If the buffer size is large enough only a single range lookup …MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
… trick MySQL into removing smaller files with DROP TABLE to minimize the effect, such as: TRUNCATE TABLE large_table; ALTER TABLE large_table ENGINE=…; DROP TABLE large_table; TRUNCATE TABLE large_table; OPTIMIZE TABLE large_table; DROP TABLE large_table; Unfortunately as it turned out each of the administrative commands like ALTER TABLE or OPTIMIZE TABLE one…
Post: MySQL: what read_buffer_size value is optimal ?
September 17, 2007 By 25 Comments
The more I work with MySQL Performance Optimization and Optimization for other applications the better I understand I have to … fast full table scans for large table you should set this variable to some high value. Sample my.cnf values on large memory sizes recommend 1M settings and MySQL…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…table_definitionsblogpercona.commanual Open_tablesblogpercona.commanual Opened_filesblogpercona.commanual Opened_table_definitionsblogpercona.commanual Opened_tablesblogpercona.commanual optimizer_prune_levelblogpercona.commanual optimizer_search_depthblogpercona.commanual optimizer…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… does which can be multiple orders of magnitude faster for large tables. There is fair number of questions about this feature which… for – for example will LOAD DATA INFILE be optimized same way as for MyISAM tables by separate phase of building Indexes ? Will… optimized on systems with 1-2 SATA drives so they are far from optimal for server grade IO subsystems. These are for MySQL…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… able to be used, which can be important for large, heavily written tables. Q: in trick #1 will “WHERE a IN (2… key significantly fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal with primary key…. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or filesort…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
…mysql> optimize table a; +——–+———-+———-+——————————————————————-+ | Table | Op | Msg_type | Msg_text | +——–+———-+———-+——————————————————————-+ | test.a | optimize | note | Table…
